CODEWITHSUNDEEP
sqlsql-serversql-server-2012
FORCODE
FORCODE

Reputation: 11

SQL Server DATEPART() function

Today I wrote two queries on Datepart() and get different returns below

Query #1:

Select Datepart(day,'2015-07-05')

returns '5', which I expected.

Query #2:

Select Datepart(day, 2015-07-05)

Returns '27', which is a little bit funny, and I don't understand how 27 is being returned.

The difference between these two queries is one with the date inside ' ', and the other without.

Anybody can help me out here?

Upvotes: 1

Views: 380

Answers (1)

Martin Smith
Martin Smith

Reputation: 452998

2015-07-05 is just a mathematical expression which adds up to the integer 2003. (Subtracting 7 from 2015 gives 2008 then subtract 5)

2003 evaluates to '1905-06-27' when implicitly cast to datetime as casting int to datetime works the same as adding that number of days to the base date of 1 Jan 1900 (i.e. equals DATEADD(DAY, 2003,'19000101')).

So this is where the 27 comes from.

The correct way to denote date literals in SQL Server is as a string '2015-07-05' (ISO format - unambiguous for newer datetime datetypes) or '20150705' (unambiguous for legacy datatypes) or using the ODBC format { d '2015-07-05' }.

Upvotes: 5

Related Questions