Solved

Doing DateTime Math with SQL Server

Posted on 2002-06-10
4
563 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:ScottPletcher
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now