4

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
Chanukya
  • 5,833
  • 1
  • 22
  • 36
Vincentgsn
  • 53
  • 5

3 Answers3

7

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];
Thom A
  • 88,727
  • 11
  • 45
  • 75
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
Chanukya
  • 5,833
  • 1
  • 22
  • 36
2

DateTime should be passed in single quotes!

Check :

DECLARE @date DATETIME = 2019-02-01
SELECT @date

Your initial query (without the quotes) involves an "implied conversion" where your date column is being converted to a varchar (using a server default format for that conversion [That is treated as the default date plus the number of days equal to the computed amount of the assigned value.])

Killer Queen
  • 776
  • 9
  • 20
  • 1
    The value `2019-02-01` isn't being converted to a `varchar` at all. Those are 3 integer values, in an expression, which is being evaluated and then implicitly converted to a `datetime`. – Thom A Feb 01 '19 at 14:27