Solved

Doing DateTime Math with SQL Server

Posted on 2002-06-10
4
566 Views
Last Modified: 2010-05-18
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
Comment
Question by:DreamingEagle
4 Comments
 
LVL 75

Accepted Solution

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

Anthony
0
 
LVL 69

Expert Comment

by:Scott Pletcher
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

by:oui_li
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

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


0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question