x
Solved

# Doing DateTime Math with SQL Server

Posted on 2002-06-10
Medium Priority
582 Views
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?

0
Question by:DreamingEagle

LVL 75

Accepted Solution

Anthony Perkins earned 200 total points
ID: 7067841
Use the DateDiff T-SQL function as follows:
DateDiff(Hour, ORDEROn, ORDEROff)

Anthony
0

LVL 70

Expert Comment

ID: 7067915
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

LVL 2

Expert Comment

ID: 7068318
If you want hours and minutes as decimal, you could just do

DATEDIFF(minute, ORDEROn, ORDEROff) /60

Will
0

LVL 1

Author Comment

ID: 7070041
DateDiff(hour,[OrderON],[OrderOFF]) worked perfectly.  Thanks...

0

## Featured Post

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.