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 |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2002-10-02 : 15:04:29
|
| I have four fields that I am working with:StartDate (Datetime)StartTime (Varchar)FinDate (Datetime)FinTime (Varchar)If the StartDate and FinDate had the time included, I could do a simple datediff to calculate total minutes between them. Since they do not, how can I convert the "time" fields that are varchar to something that will allow me to do this calculation.**Keep in mind that if it it spans a day, and the the end time is less than the start time, it will appear as a negative.Any ideas?MBeal |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-02 : 15:33:15
|
quote:
If the StartDate and FinDate had the time included, I could do a simple datediff to calculate total minutes between them...
Why DON'T the StartDate and FinDate columns include the time? There's no need to have the StartTime and FinTime columns at all. |
 |
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2002-10-02 : 15:42:04
|
| Because the program was setup with these fields -- I'm just extracting the data and manipulating it accordingly. This is what I have now...Select M_RoomNo, Cast(LEFT(M_EntDate,12)+M_EntTime as datetime) [Start], Cast(LEFT(M_FinDate,12)+M_FinTime as datetime) [Finish], Cast(DateDiff(mi,Cast(LEFT(M_EntDate,12)+M_EntTime as datetime), Cast(LEFT(M_FinDate,12)+M_FinTime as datetime)) as decimal) [Minutes]From MaintWhere M_Completed = 1 and M_EntDate > '09/30/2002' Order by Cast(DateDiff(mi,Cast(LEFT(M_EntDate,12)+M_EntTime as datetime), Cast(LEFT(M_FinDate,12)+M_FinTime as datetime)) as decimal) descMBeal |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-10-02 : 17:50:04
|
| DATEDIFF(minute, StartDate + CONVERT(datetime, StartTime, 108), EndDate + CONVERT(datetime, EndTime, 108))seems like the most obvious way to me. |
|
|
|
|
|
|
|
|
Calculate Time Difference