Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Question 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.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    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.

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    This will work.

    SET DATEFIRST 1 -- declares Monday is first day of week - alter to suit your situation

    SELECT DATENAME(dw,(getdate()))
    SELECT DATENAME(MONTH, getdate())

    Cheers

    SG

  4. #4
    Join Date
    Sep 2003
    Posts
    3

    Thumbs up

    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.

    Ozz

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    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.

    HTH

    Cheers,

    SG.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •