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: 246686

  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() )

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• 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: 183698

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