DATETIME data type arithmenic in SQL Server 2005

[quiptro]

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

[davidcairns]

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)

[davidcairns]

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 :)

[quiptro]

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.

[davidcairns]

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