Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Subtract two dateTime fields

Posted on 2009-03-31
10
Medium Priority
?
1,068 Views
Last Modified: 2012-05-06
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
Comment
Question by:u2envy1
  • 5
  • 4
10 Comments
 
LVL 16

Assisted Solution

by:brad2575
brad2575 earned 200 total points
ID: 24028461
DATEDIFF ( datepart , startdate , enddate )

date parts listed here:
http://msdn.microsoft.com/en-us/library/ms189794.aspx
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1800 total points
ID: 24028472
Try something like this:


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

Expert Comment

by:Patrick Matthews
ID: 24028486
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:u2envy1
ID: 24028589
matthewspatrick:
The CONVERT is not stripping the date properly. Im getting 12/31/1899 & 01/01/1900
0
 

Author Comment

by:u2envy1
ID: 24028634
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24028635
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
 

Author Comment

by:u2envy1
ID: 24028713
Thx Matthew.....
How do I convert the As Diff to show hh:mm only. No seconds ?
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1800 total points
ID: 24028741
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
 

Author Closing Comment

by:u2envy1
ID: 31564795
Thx, its working perfectly......
0
 

Author Comment

by:u2envy1
ID: 24036338
One more thing. Is it possible to Sum(Diff) ?
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

581 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