Count days between 2 dates - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Count days between 2 dates

Author

Topic

Marteijn
Starting Member

28 Posts

Posted - 2011-10-21 : 05:41:14

Hello,

I have a problem in Visual Studio. I have two columns with dates. In last column I want to calculate the difference in days between those dates.

Example:

SELECT ADRESNUMMER, DD_DOCUMENT, CREATION, CREATION - DD_DOCUMENT AS VERSCHIL
FROM fact_BAG_Adressen AS a
WHERE (MUTATIEREDEN = 34) AND (CREATION >= CONVERT(DATETIME, '2011-04-01 00:00:00', 102)) AND (CREATION - DD_DOCUMENT > 4)
ORDER BY ADRESNUMMER

Result:

0748200000001220 16-4-2010 0:00:00 12-4-2011 10:27:45 28-12-1900 10:27:45
0748200000001221 16-4-2010 0:00:00 12-4-2011 10:29:26 28-12-1900 10:29:26
0748200000001222 15-3-2011 0:00:00 12-4-2011 10:35:55 29-1-1900 10:35:55

The last column (VERSCHIL) needs to be converted to numbers. Problem also with that is the time in the date. If I do this in Excel, the result will be 5 days, but the right result is 4 days.

How can I do this in Visual Studio? I tried to do some formatting on the columns and the time is not displayed afterwards but is still present 'underwater'...

What I want to see is:

0748200000001220 16-4-2010 12-4-2011 4
0748200000001221 16-4-2010 12-4-2011 4
0748200000001222 15-3-2011 12-4-2011 28 (think so ;-))

Thank you already!
Marteijn

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-10-21 : 05:48:33

select DATEDIFF(dd,'2011-04-16 10:27:45', '2011-04-12 10:27:45')

obviously the result will be -4 and not 4..