Post reply

find the start date and end date for the period Getdate() falls into.

  • November 3, 2022 at 2:13 pm

    #4108249

    I have a table that has start date and end date for each period of the year. I need to get the start date and end date for the period that getdate() should be in. SO if getdate() is '11-05-2022' it would be in period 11 which should return start date of '10-31-2022' and end date of '11-25-2022'

    select convert(varchar, glfStartDate,101) as StartDate, convert(varchar, glfEndDate,101) as EndDate from GLFiscalYearPeriods where glfGLFiscalYearID=year(getdate())

    I could not decide how to do it. A case statement?

    here is the table

    glfGLFiscalYearIDglfGLFiscalYearPeriodIDStartDateEndDate
    2022101/01/202201/31/2022
    2022202/01/202202/28/2022
    2022303/01/202204/01/2022
    2022404/02/202204/30/2022
    2022505/01/202205/31/2022
    2022606/01/202207/01/2022
    2022707/02/202207/31/2022
    2022808/01/202208/26/2022
    2022908/29/202209/30/2022
    20221010/01/202210/28/2022
    20221110/31/202211/25/2022
    20221211/28/202212/31/2022

  • November 3, 2022 at 2:31 pm

    #4108250

    It's just a filter:

    WHERE getdate() >= glfStartDate AND getdate() <= glfEndDate

  • November 3, 2022 at 3:01 pm

    #4108274

    Thanks

  • November 3, 2022 at 9:38 pm

    #4108422

    ratbak wrote:

    It's just a filter:

    WHERE getdate() >= glfStartDate AND getdate() <= glfEndDate

    This won't work with GETDATE() because of the included time.  You either need to convert getdate() to a date - or you need to change the range to an open interval:

    WHERE getdate() >= glfStartDate AND getdate() < DATEADD(day, 1, glfEndDate)

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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