Why is DATENAME(GETDATE()) giving a different result as DATENAME(2019-02-01) when that is the date today?

Question

I don't understand why DATENAME(GETDATE()) gives a different result from DATENAME(2019-02-01) When that is today's date

SELECT GETDATE(), DATENAME(w, GETDATE()),DATENAME(dw, 2019-02-01)

Returns:

2019-02-01 14:51:46.017 Friday  Monday<br>

While I would expect it to return:

2019-02-01 14:51:46.017 Friday  Firday

Answer 1

2 reasons. Firstly, you're using the value 2019-02-01; which is the expression is "2019 minus 2 minus 1"; which evaluates to 2016. 2016 as a datetime is the date '1905-07-10' (which is 2,016th day after '1900-01-01'). If you get the value of WEEKDAY using the function DATENAME of '1905-07-10' you get 'Monday'.

If you change the value to a string, however, because you're (probably) still using datetime, the value '2019-02-01' would be interpreted in the format yyyy-dd-MM, meaning you get the value 'Wednesday' (SELECT DATENAME(WEEKDAY,CONVERT(datetime,'2019-02-01'));), which is the weekday that 02 January 2019 was.

To get the right result, use a non-ambiguous literal string:

SELECT DATENAME(WEEKDAY,'20190201');

Non-ambiguous date(time) formats (regardless of datatype) in SQL Server are yyyyMMdd and yyyy-MM-ddThh:mm:ss.ssss (The format yyyy-MM-dd is non ambiguous if you aren't using datetime, notice that if you run the following SQL, the value for datetime is different:)

SELECT CONVERT(date,'2019-02-01') AS [date],
       CONVERT(datetime,'2019-02-01') AS [datetime],
       CONVERT(datetime2(0),'2019-02-01') AS [datetime2],
       CONVERT(datetimeoffset,'2019-02-01') AS [datetimeoffset];

Answer 2

Please use this datetime should be in string format(date format)

 SELECT GETDATE(), DATENAME(w, GETDATE()),DATENAME(dw, '2019-02-01')

output

2019-02-01 14:01:38.343          Friday          Friday