Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

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?

0
DreamingEagle
Asked:
DreamingEagle
1 Solution
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now