• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1078
  • Last Modified:

Subtract two dateTime fields

How do I two subtract a DateTime fields in sql
Feild1 = 2009/04/05 08:00, Field2 = 2009/05/02 09:00
The dates should not be counted in the subtraction. The value to be returned should be 01:00.
0
u2envy1
Asked:
u2envy1
  • 5
  • 4
3 Solutions
 
brad2575Commented:
DATEDIFF ( datepart , startdate , enddate )

date parts listed here:
http://msdn.microsoft.com/en-us/library/ms189794.aspx
0
 
Patrick MatthewsCommented:
Try something like this:


SELECT Field1, Field2, CONVERT(datetime, CONVERT(varchar, Field2, 108)) - CONVERT(datetime, CONVERT(varchar, Field1, 108)) AS Diff
FROM SomeTable
0
 
Patrick MatthewsCommented:
The "round trip" CONVERT expressions have the effect of stripping the date portions of the datetime values;
similarly, had I used the format code 101 instead of 108, it would have stripped the time portions.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
u2envy1Author Commented:
matthewspatrick:
The CONVERT is not stripping the date properly. Im getting 12/31/1899 & 01/01/1900
0
 
u2envy1Author Commented:
If I have a time Value of 08:00 AM & 07:00 PM I would like a value of 12:00 returned with no AM / PM.
Will the Convert approach do this for me ?
0
 
Patrick MatthewsCommented:
u2envy1,

datetime values always encode a date and a time.  To enforce formatting:

SELECT Field1, Field2, CONVERT(varchar, CONVERT(datetime, CONVERT(varchar, Field2, 108)) - CONVERT(datetime, CONVERT(varchar, Field1, 108)), 108) AS Diff
FROM SomeTable

Regards,

Patrick
0
 
u2envy1Author Commented:
Thx Matthew.....
How do I convert the As Diff to show hh:mm only. No seconds ?
0
 
Patrick MatthewsCommented:
SELECT Field1, Field2, LEFT(CONVERT(varchar, CONVERT(datetime, CONVERT(varchar, Field2, 108)) - CONVERT(datetime, CONVERT(varchar, Field1, 108)), 108), 5) AS Diff
FROM SomeTable
0
 
u2envy1Author Commented:
Thx, its working perfectly......
0
 
u2envy1Author Commented:
One more thing. Is it possible to Sum(Diff) ?
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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