CODEWITHSUNDEEP
sqlsql-servert-sql
kubaklamca
kubaklamca

Reputation: 143

Weird results of date conversion (DATENAME,DATEPART) using SQL Server

If I use this query:

SET DATEFORMAT dmy;
SELECT DATEPART(dw,CAST('02-08-2018' AS date)) AS day

I'll have result 5 when 02-08-2018 is Thursday.

What is even weirder in DATENAME week starts from 0, so 0 is Monday and if I use something like this:

SET DATEFORMAT dmy;
    SELECT DATENAME(dw,DATEPART(dw,CAST('02-08-2018' AS date))) AS day

I will get Saturday, but if I'm just using DATENAME just on casted date:

SET DATEFORMAT dmy;
    SELECT DATENAME(dw,CAST('02-08-2018' AS date)) AS day

I will get Thursday- correct result.

My question is: What is happening especially in the first query that is giving me back 5? And if DATENAME(dw,...) starts from 0, is DATEPART(dw,...) starting from 0 too?

Upvotes: 0

Views: 218

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

There is nothing weird there. With date set to DMY your '02-08-2018' would mean August 2nd, 2018 which is Thursday. Also, weekdays start from Sunday, thus it gives 5 for the weekday.

In your second query:

SET DATEFORMAT dmy;
SELECT DATENAME(dw,DATEPART(dw,CAST('02-08-2018' AS date))) AS day

you are not checking date name for August 2nd, 2018 but for 5 which is casted implicitly to a date and is a saturday. IOW you are sort of doing:

SELECT datename(dw,DATEADD(d,5,CAST('00010101' AS DATE)))

Odd and undocumented maybe but it is how SQL server is behaving.

When you cast to a date you get the Thursday as expected.

Anyway, SQL server also have problems in converting strings to a date\datetime based on language and settings, it is more reliable not to depend on language and settings and specify the date as 'yyyyMMdd'. ie:

SELECT DATEPART(dw,CAST('20180802' AS date)) AS day

EDIT: BTW datename(dw, ...) has nothing like start from 0 or 1. It just returns the day of week name.

Upvotes: 1

Related Questions