Doing DateTime Math with SQL Server

I am converting an app from MS Access 2000 to use a VB front end with SQL Server as the database.

I have two datetime fields ORDEROn and ORDEROff.  When I subtract ORDEROn from ORDEROff in a SQL statement in MS Access I get the number of hours in decimal format.  But SQL Server returns 1901-01-01 00:00:0.000 no mater what the values are in the two DateTime fields.  

How can I make SQL Server return the number of hours and minutes of difference between these two fields?

LVL 1
DreamingEagleAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Use the DateDiff T-SQL function as follows:
DateDiff(Hour, ORDEROn, ORDEROff)

Anthony
0
 
Scott PletcherSenior DBACommented:
Since you want minutes as a decimal also, I think you have to use a method similar to this:

DECLARE @hrsDiff DECIMAL(4,2)

SET @hrsDiff = DATEDIFF(hour, ORDEROn, ORDEROff) +
CAST(CAST(DATEDIFF(minute, ORDEROn, ORDEROff) % 60 AS DECIMAL(4,2)) / 60.0 AS DECIMAL(4,2))

That's ugly, I know, but AFAIK there's not an easier way to do it.
0
 
oui_liCommented:
If you want hours and minutes as decimal, you could just do

DATEDIFF(minute, ORDEROn, ORDEROff) /60

Will
0
 
DreamingEagleAuthor Commented:
DateDiff(hour,[OrderON],[OrderOFF]) worked perfectly.  Thanks...


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.