var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: urgently need help with dateparts using t-sql
I am getting incorrect daynames from my t-sql query even though SQL Server seems to know the correct month and day.
The sql I am using is:
SELECT DATENAME(WEEKDAY,DAY(bookingdate)) AS Dayname,
DATENAME(MONTH, bookingdate) As sMonth, bookingdate FROM appointments
but the resultset does not always give the correct dayname for the date as seen below:
Dayname sMonth bookingdate
Saturday August 2003-08-27 00:00:00.000
Tuesday September 2003-09-02 00:00:00.000
The 2nd Sep 2003 is indeed a Tuesday but the 27th August is not a Saturday.
the query gives proper results on my machine.I think u should check the local timezone settings on u'r server and the time format on sql server.
This will work.
SET DATEFIRST 1 -- declares Monday is first day of week - alter to suit your situation
SELECT DATENAME(MONTH, getdate())
Thanks for the reply SG.
However, the actual date i am using in my query is stored in a table. I am not using the Getdate function. What i am trying to obtain basically is the dayname for any given date stored in my table(s).
What is happening is that the Dayname i get is not correct or consistent despite the fact that is seems to get the correct month and day part.
any further suggestions will be greatly appreciated.
Well if I use this code:
Remember too I use UK date format......i.e. DD/MM/YYYY
SELECT DATENAME(DW, '27/08/2003' ) I get 'Wednesday'
SELECT DATENAME(MONTH, '27/08/2003' ) I get 'August'
SELECT DATENAME(DW, '02/09/2003' ) I get 'Tuesday'
SELECT DATENAME(MONTH, '02/09/2003' ) I get 'September'
So I suspect your machine may be not set up correctly..... check the setting for the SETFIRST command using :
SELECT @@DATEFIRST - if you run this command on a Monday this should return 1 . If not, you may need to run this command :
SET DATEFIRST X where X = 1 ( if you want first day of week to be monday etc ) . See BOL for more info.