0

Hi,

I am writing a query that requires as input the difference in days from two dates (Project_End - Project_Start, both of data type DATETIME), how can I find it? Can I subtract the two dates?

Thanks,
Robert

2
Contributors
4
Replies
5
Views
10 Years
Discussion Span
Last Post by davidcairns
0

You can subtract them (the difference will be in days, and include fractions if you have times). However you may need to use CAST and/or CONVERT to make it come out correctly.

The following example returns 28.0
select cast(cast('01 Jan 2006' as datetime) - cast('04 Dec 2005' as datetime) as float)

0

Be very careful of date string formats also, if you have an ambiguous format (such as dd/mm/yyyy or mm/dd/yyyy) then use convert instead of cast and be specific.

Sorry for the dp :)

0

Hi,

Thanks for the help. I used CAST and it works perfectly. All the dates are in the format mm/dd/yyyy so I didn't get any problems nor mistakes.

0

In that case I would use the following
cast(convert(datetime, datestring1, 101) - convert(datetime, datestring2, 101) as float)

It's a lot safer to be explicit about ambiguous formats

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.