DATEPART / DATENAME

• Johan_1975

  Right there with Babe

  Points: 724

  More actions

  June 21, 2016 at 6:26 am

  #317294

  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 **

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  June 21, 2016 at 6:37 am

  #1886000

  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

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  June 21, 2016 at 6:46 am

  #1886004

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 21, 2016 at 6:53 am

  #1886010

  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

• Johan_1975

  Right there with Babe

  Points: 724

  More actions

  June 21, 2016 at 7:00 am

  #1886013

  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 **