Post reply

DATENAME using week as the datepart

  • February 8, 2007 at 7:31 am

    #117479

    Hello all,

    Im using the following in a query

    SELECT sess.attendDate, DATENAME(dw, sess.attendDate)AS Day, DATENAME(ww,sess.attendDate)AS WEEK From sessions sess

    and its bringing back the Weeknumber for a date, but I need to start the week at monday rather than Sunday and its doing the following:

    18/09/2006Monday38
    19/09/2006Tuesday38
    20/09/2006Wednesday38
    21/09/2006Thursday38
    22/09/2006Friday38
    23/09/2006Saturday38
    24/09/2006Sunday39

    is there any way of changing the order on this so sunday is still week 38 rather than the beginning of week 39?

    I tried adding SET DATEFIRST 1 to the beginning of the query but it didnt seem to make a difference

    Thanks

    Debbie

  • February 8, 2007 at 7:39 am

    #688570

    Hi. Confusing this one...

    Can you post the DDL of the sessions table and the query you're using?

    Try this and see if you get the correct results using today's date as a reference... If you don't get Sunday and Monday as different weeks it's probably a configuration thing.

    SET DATEFIRST 1

    SELECT DATEPART(ww, GETDATE() -5),  DATENAME(dw,GETDATE() -5)

    UNION ALL

    SELECT DATEPART(ww, GETDATE() -4),  DATENAME(dw,GETDATE() -4)

    UNION ALL

    SELECT DATEPART(ww, GETDATE() -3),  DATENAME(dw,GETDATE() -3)

    UNION ALL

    SELECT DATEPART(ww, GETDATE() -2),  DATENAME(dw,GETDATE() -2)

    UNION ALL

    SELECT DATEPART(ww, GETDATE() -1),  DATENAME(dw,GETDATE() -1)

    UNION ALL

    SELECT DATEPART(ww, GETDATE()),  DATENAME(dw,GETDATE())

    Ade


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • February 8, 2007 at 7:47 am

    #688572

    Ah ha,

    Not sure how I managed it but I used -1 against the query and this works fine.

     

    Thanks

    Debbie

Viewing 3 posts - 1 through 2 (of 2 total)

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