Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
javad.nikoo
Starting Member
17 Posts |
 Posted - 2011-12-07 : 03:37:57
|
| i have time sheet table i store 2 time in this table start time and end time with this format('12:00') what i want is calculating minute i use datediff but i have a problem with this function for example my start time is '23:30' to '02:00' bu the result is incorrect the query i use is these(select DATEDIFF(minute, convert(datetime, '23:30'), convert(datetime, '02:00')))but what i want is these it must show me 150 min Best Regard'sThanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-12-07 : 03:56:02
|
[code]select case when start_time < end_time then datediff(minute, start_time, end_time) else datediff(minute, end_time, dateadd(day, 1, start_time)) end[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-07 : 04:13:45
|
| [code]select CASE WHEN DATEDIFF(mi,startime,endtime)<0 THEN 24*60 ELSE 0 END + DATEDIFF(mi,startime,endtime) from table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-12-08 : 02:37:00
|
quote:
Originally posted by khtan
select case when start_time < end_time then datediff(minute, start_time, end_time) else datediff(minute, end_time, dateadd(day, 1, start_time)) end
KH[spoiler]Time is always against us[/spoiler]
If start_time is of time datatype, you cannot use dateadd functionMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
dateDiff