DATEPART / DATENAME

  • Hello,

    We're Tuesday. It's important for what's coming.

    I encountered an issue on the DATENAME function and need some help.

    If I type :

    SELECT DATEPART(dw,GETDATE())

    It returns 2 (for Tuesday)

    If I type

    SELECT DATENAME ( weekday , 2 )

    it returns mercredi (wednesday, I'm in France) so I assume than in DATEPART SQL Server begin the week with the Monday, and in DATENAME SQL Server begin the week with the Sunday.

    Am I right ?

    Is there a way to force DATENAME to begin the week with Monday ?

    Thanks For your lights!

    Johan Jezequel
    ** Sorry for my english, I promess you I do my best **

  • Johan_1975 (6/21/2016)


    Hello,

    We're Tuesday. It's important for what's coming.

    I encountered an issue on the DATENAME function and need some help.

    If I type :

    SELECT DATEPART(dw,GETDATE())

    It returns 2 (for Tuesday)

    If I type

    SELECT DATENAME ( weekday , 2 )

    it returns mercredi (wednesday, I'm in France) so I assume than in DATEPART SQL Server begin the week with the Monday, and in DATENAME SQL Server begin the week with the Sunday.

    Am I right ?

    Is there a way to force DATENAME to begin the week with Monday ?

    Thanks For your lights!

    Your second example is incorrect. It should have been

    SELECT DATENAME ( weekday , GETDATE() )

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Yes, the 2 is converted into a date - 3rd January 1900, so you'll always get Wednesday for that, no matter what your language settings.

    John

  • Adding to Phil's comment. Your second example is using an integer. This integer is being converted to a datetime before applying the datename function. The number 2 is converted to 03-Jan-1900 which was a Wednesday.

    SELECT CAST(2 AS datetime)

    Also, the day of the week using DATEPART is dependant on the language and overriden when using SET DATEFIRST on the current batch.

    SET DATEFIRST 7;

    SELECT DATEPART(dw,GETDATE());

    SET DATEFIRST 1;

    SELECT DATEPART(dw,GETDATE());

    SET DATEFIRST 2;

    SELECT DATEPART(dw,GETDATE());

    SET DATEFIRST 3;

    SELECT DATEPART(dw,GETDATE());

    SET LANGUAGE us_english;

    SELECT DATEPART(dw,GETDATE());

    GO

    SET LANGUAGE us_english;

    SELECT DATEPART(dw,GETDATE());

    GO

    SET LANGUAGE french;

    SELECT DATEPART(dw,GETDATE());

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks all for your quick replies!

    It works better with a getdate(), indeed!

    Johan Jezequel
    ** Sorry for my english, I promess you I do my best **

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply