Date parts with DateDiff - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date parts with DateDiff

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2012-08-13 : 23:32:00

I need to find the number of seconds between Date1 and Date2. I understand the basic logic of DateDiff like this:

SELECT DateDiff(Ss,'2012-08-13 08:58:12', '2012-08-13 08:59:18')
This will return 66 seconds, which is perfect.

However, my table has different fields for every date part. So using example above, my table data would look like:

DateOfBusiness = 2012-08-13
FirstHour = 8
firstMin = 58
FirstSec = 12
LastHour = 8
LastMin = 59
LastSec = 18

So
1) I need to convert all of these fields together to create yyyy-mm-dd hh:mm:ss
2) I need to subtrack the two datetimes using DateDiff
3) I need to take in account if the Last* is > First* and if so, Im assuming use DateAdd to DateOfBusiness.

I have tried many different ways to get the dates formatted correctly and I just cannot get it just right. Im not sure if it is supposed to be Cast or Convert and then I have even tried Nvarchar to create the time string from the fields, but then I cannot do th DateDiff.

Any advice?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-13 : 23:57:42

[code]
diff = case when ((LastHour * 60 * 60) + (LastMin * 60) + LastSec)
> ((FirstHour * 60 * 60) + (FirstMin * 60) + FirstSec)
then ((LastHour * 60 * 60) + (LastMin * 60) + LastSec)
- ((FirstHour * 60 * 60) + (FirstMin * 60) + FirstSec)
else ((LastHour * 60 * 60) + (LastMin * 60) + LastSec)
- ((FirstHour * 60 * 60) + (FirstMin * 60) + FirstSec)
+ (24 * 60 * 60)
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

JAdauto
Posting Yak Master

160 Posts

Posted - 2012-08-14 : 11:26:14

This is great. I was very close, but I couldn't figure out how to get the + day in there. So, thank you. This is where I am now:

SELECT CASE
WHEN FKRevenueId = 3 THEN 0
ELSE
CASE WHEN ((LastOrderHour * 3600) + (LastOrderMinute * 60) + LastOrderSecond)
> ((FirstOrderHour * 3600) + (FirstOrderMinute * 60) + FirstOrderSecond)
THEN ((LastOrderHour * 3600) + (LastOrderMinute * 60) + LastOrderSecond)
- ((FirstOrderHour * 3600) + (FirstOrderMinute * 60) + FirstOrderSecond)
ELSE ((LastOrderHour * 3600) + (LastOrderMinute * 60) + LastOrderSecond)
- ((FirstOrderHour * 3600) + (FirstOrderMinute * 60) + FirstOrderSecond)
+ (24 * 3600)
END as TTL
END
FROM dbo.TurnTime
WHERE StoreID = 1111
AND DateOfBusiness = '2012-07-11'
GROUP BY DayPartId, RevenueId, DateOfBusiness

Im throwing the syntax off because I guess Im trying to do two case statements. I want TTL to be 0 if FKRevenueID = 3, otherwise, I want to do that calculation. If I move END as TTL to the middle Case, I get this error:
"Column 'dbo.dpvHstGndTableTurn.LastOrderHour' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
error.

I dont want to group by all the different times. So, I if I move as TTL to the outside Case, I get this error:
"Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'as'."

Any advice for my syntax?

Thx

JAdauto
Posting Yak Master

160 Posts

Posted - 2012-08-14 : 12:41:40

I just added the items in blue and now my syntax is no longer throwing error and it seems to be working for ONE specific ticket. Does that look like a reasonable solution?

SELECT Sum( CASE
WHEN FKRevenueId = 3 THEN 0
ELSE
CASE WHEN ((LastOrderHour * 3600) + (LastOrderMinute * 60) + LastOrderSecond)
> ((FirstOrderHour * 3600) + (FirstOrderMinute * 60) + FirstOrderSecond)
THEN ((LastOrderHour * 3600) + (LastOrderMinute * 60) + LastOrderSecond)
- ((FirstOrderHour * 3600) + (FirstOrderMinute * 60) + FirstOrderSecond)
ELSE ((LastOrderHour * 3600) + (LastOrderMinute * 60) + LastOrderSecond)
- ((FirstOrderHour * 3600) + (FirstOrderMinute * 60) + FirstOrderSecond)
+ (24 * 3600)
END
END ) as TTL
FROM dbo.TurnTime
WHERE StoreID = 1111
AND DateOfBusiness = '2012-07-11'
GROUP BY DayPartId, RevenueId, DateOfBusiness

JAdauto
Posting Yak Master

160 Posts

Posted - 2012-08-14 : 12:44:59

My seconds difference for a one ticket is correct. My my numbers for one day part look insanely large.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources