subtracts 2 dates in SQL & return datetime format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

subtracts 2 dates in SQL & return datetime format

Author

Topic

nhuanlieu
Starting Member

27 Posts

Posted - 2009-09-18 : 15:12:15

Hi, how do I I return an actual datetime format rather than the 1900-01-series like below when I subtracts 2 dates in SQL.

Select top 10 DateA, DateB, DateA - DateA as DateC
From table:

DateA DateB DateC (A-B)
2007-08-07 2007-08-01 1900-01-07 <-- wants to be 2007-08-06

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-18 : 16:26:07

That answer is a datetime.
sql server stores dates as an offset from 1900-01-01. The difference between dateA and dateB is 6 days. So the 1900-01-01 + 6 days is 1900-01-07.
By what logic do you think the answer should be 2007-08-06 ?

You can also use DATEADD to add or subtract days from a datetime to get a new datetime.

Be One with the Optimizer
TG

nhuanlieu
Starting Member

27 Posts

Posted - 2009-09-18 : 16:55:46

I see your point. Thanks much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-19 : 02:36:12

It should be something like this



select DateA,DateB,DATEADD(day,datediff(day,DateB,DateA)-1,dateadd(month,datediff(month,0,DateB),0)) from
(
select 
	CAST('2007-08-07' as datetime) as DateA,CAST('2007-08-01 ' as datetime) as DateB union all
	select '2009-06-26','2009-06-11'
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources