Using CAST to return Integer from SmallDateTime

[Andy7777]

Hi,
I need to convert dates in SmallDateTime format into Intergers. When I use CAST on '25/07/2003 11.26', SQL Server returns 37825. However, Excel spreadsheet returns 37827 when I format the date. CAST through SQL Server is exactly 2 less than Excel (even when using floating point conversion) Why is it different?
Thanks for your help.

Andy

 

[Andy7777]

Also,
CAST(DateTime AS INT) gives 37826
CAST(DateTime AS REAL) gives 37825.508
CAST(CAST(DateTime AS REAL) AS INT) gives 37825
DateTime = '2003-07-25 12:09:00'
Why is variable to INT different from variable to REAL to INT?

 

[swampBoogie]

What you get when casting a date to int is the number of days since 1900-01-01.

Excel uses another base date when calculating the number of days, possibly 1899-12-31 as the difference is one day.

You could use

datediff(day,@dateTimeValue,'1899-12-31')

As to the difference, the behaviour seems somewhat inconsistent.

 

[Andy7777]

swampBoogie,
Thanks for your help. I can work around it but it's not what you'd expect. Andy.