| Author |
Topic |
|
kallu
Starting Member
6 Posts |
 Posted - 2007-09-11 : 05:27:13
|
| hi, As per my project requirement ,i have to store only time format data ( Ex 6:00 AM).My table column data type is small date and time. plz help me. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-11 : 05:31:38
|
| declare @d smalldatetime set @d='6:00:00 AM'select @dMadhivananFailing to plan is Planning to fail |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 05:37:23
|
| that won't work Madhi... this is still 2005Store it as string type or int type in minutes--------------------keeping it simple... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-11 : 05:51:35
|
quote:
Originally posted by jen
that won't work Madhi... this is still 2005Store it as string type or int type in minutes--------------------keeping it simple...
Well. I didnt see the Forum name  MadhivananFailing to plan is Planning to fail |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 05:53:16
|
| does it work in 2008? Amazing!--------------------keeping it simple... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-11 : 05:55:57
|
Why? Always use proper datatype.Why even bother about FORMATTING in the database? It is the client, not database, that should format datetime value, not database.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 05:59:20
|
it's all about user requirements  and customer is always right... so they think...give them something better and they'd say... that's not what i asked for--------------------keeping it simple... |
|
|
|
kallu
Starting Member
6 Posts |
Posted - 2007-09-11 : 06:07:00
|
| is there any function that shows difference between two times. here time format Ex is : 6:00 Am - 8 :00 AM. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
kallu
Starting Member
6 Posts |
Posted - 2007-09-11 : 06:16:18
|
| thank you peso, i have small doubt For example :my table column data type is varchar , the data stored in this column as 6:00 Am format.with explict cast is it possible get diffents between two times in mints |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-11 : 06:21:31
|
Yes you can, but all comparisons will be much harder than necessary!How to sort, for example? 9:00 AM is earlier than 4:00 PM, but 4:00 PM will come first if sorted ascending!You have to do ORDER BY CAST(Col1 AS SMALLDATETIME)which means you force a table scan and you can't use any index for that column! Which means your query will be slower than necessary.If you use SMALLDATETIME daattype, all sorts and other comparisons will happen as you think they would.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-11 : 06:22:12
|
SELECT DATEDIFF(SECOND, CAST(Col1 AS SMALLDATETIME), CAST(Col2 AS SMALLDATETIME)) / 60.0 AS MinutesFROM Table1
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 06:34:29
|
| maybe he has a date field, since he has a time field, logically the time is irrelevant/useless if you store data on different dates without saving the date?--------------------keeping it simple... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-11 : 06:43:35
|
Even worse approach.You suggest he is having Date1, Time1 and Date and Time2 columns? Nice...Now try to calculate the difference in minutes between to two times!DATEDIFF(MINUTE, CAST(Date1 AS DATETIME) + CAST(Time1 AS DATETIME), CAST(Date2 AS DATETIME) + CAST(Time2 AS DATETIME))Instead of s simple DATEDIFF(MINUTE, DateTime1, DateTime2)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 06:49:47
|
| , well he can't really expect to just have the time? even for scheduling purpose, you still need to store the dateunless....he is actually refreshing the table on a daily basis just keeping one day's worth of whatever needs to have time stamped?--no i'm not the devil's advocate but it might be a fun job--------------------keeping it simple... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-11 : 08:27:31
|
| Note that OP specified My table column data type is small date and time. MadhivananFailing to plan is Planning to fail |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
date and time