Post reply

Declare start date as yesterday and end date as today

  • March 28, 2019 at 4:17 am

    #2033910

    HI Guys,

    I found the below in a forum which i thought worked great until i drilled down to the data.  When i run this today its pull in data with a date of today when i tought it would only pick up yesterdays date. or Friday if today is a Monday. 

    ------------------------

    -- set the start date yesterday or Friday if yesterday is Sunday

    DECLARE @sd AS datetime =

    CASE DATEPART(weekday, DATEADD(day, -1, GETDATE()))

    WHEN 1 THEN CONVERT(date, DATEADD(day, -3, GETDATE()))

    ELSE CONVERT(date, DATEADD(day, -1, GETDATE()))

    END;

    -- set end dates - today

    DECLARE @ed AS datetime = CONVERT(date, GETDATE());

    Select @sd As StartDate,@ed As EndDate;

  • March 28, 2019 at 4:25 am

    #2034067

    No, that isn't what it'll do. If the day is monday the the start date will be the sunday and the end date the monday. If it is Tuesday it'll provide the dates for Saturday and Tuesday. If you want logic for the start of yesterday, to the end of today you should be doing something like:

    WHERE YourDateColumn >= CONVERT(date,DATEADD(DAY, -1, GETDATE()))
    AND YourDateCoumn < CONVERT(date,DATEADD(DAY, 1, GETDATE()))

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • March 28, 2019 at 4:30 am

    #2034068

    Thanks for the reply.  What i was hoping for was only show the previous day unless its a Monday then show Friday, Saturday and Sunday.

  • March 28, 2019 at 5:12 am

    #2034070

    craig.jenkins - Thursday, March 28, 2019 4:30 AM

    Thanks for the reply.  What i was hoping for was only show the previous day unless its a Monday then show Friday, Saturday and Sunday.

    [/quote]

    So, if it's Monday, show Friday-Sunday, if it's Tuesday show Monday & Tuesday or just Monday? I suspect what you are therefore after is:

    DECLARE @StartDate date;
    DECLARE @EndDate date;
    SET @StartDate = CASE DATEPART(WEEKDAY, GETDATE()) WHEN 1 THEN DATEADD(DAY, -3, GETDATE())
                         ELSE DATEADD(DAY, -1, GETDATE())
          END;
    SET @EndDate = GETDATE();
    ...
    WHERE YourDateColumn >= @StartDate
    AND YourDateCoumn < @EndDate

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • March 28, 2019 at 5:25 am

    #2034073

    Thank you appreciate it.  Yes so if its Tuesday only show Monday not Monday and Tuesday

  • March 29, 2019 at 1:05 pm

    #2034221

    Are you simply wanting to not include weekend days (Saturday, Sunday), or are you trying to find the previous BUSINESS day?

    If you are looking for the previous business day, you should also take into account holidays.    If that is the case I would suggest creating a calendar table.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 6 posts - 1 through 5 (of 5 total)

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