Post reply

date

  • November 11, 2014 at 4:42 am

    #312398

    Start date must be the first calendar day of a month or a Monday

    I can get the first day SELECT DATEADD(day,- (DAY(GETDATE()) - 1),GETDATE())

    how to find if it is a monday?

    cheers

  • November 11, 2014 at 6:22 am

    #1757667

    -- day 0 ('19000101') is a monday

    SELECT DATENAME(dw,cast('19000101' as date)) -- monday

    SELECT DATEDIFF(day,0,GETDATE()) -- 41952 (tuesday 11th November)

    SELECT 41952 % 7 -- = 1 for today (tuesday)

    SELECT 41951 % 7 -- = 0 for yesterday (monday)

    -- so:

    SELECT DATEDIFF(day,0,GETDATE()) % 7 ;

    -- will always be 0 for mondays.

    WITH TwentyDates AS (

    SELECT aDate = DATEADD(DAY,n,GETDATE())

    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)) d (n)

    )

    SELECT *, DATENAME(dw,aDate), DATEDIFF(day,0,aDate) % 7

    FROM TwentyDates

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 12, 2014 at 4:39 am

    #1757908

    resloved

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

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