Post reply

IF statement using a Date range

  • October 25, 2009 at 1:28 pm

    #136640

    Hi Guys, got a question. I have to select data based on a date range between 12/9/2009 and 3/17/2010. After 3/17/2010 I have to use a different criteria. I tried using the IF statement but couldn't figure out how to configure it. I tried using between, tried AND with 2 selects, and my personal favorite concatinated IF statements.

    Any ideas how I can accomplish this task?

    Thanks much,

  • October 25, 2009 at 1:54 pm

    #1070451

    Hi,

    Assuming that you would like your second criteria set to return the same fields:

    SELECT Field1, Field2, Field3

    FROM YourTable

    WHERE DateField >= '12-9-2009' AND DateField < '4-17-2010'

    UNION ALL

    SELECT Field1, Field2, Field3

    FROM YourTable

    WHERE DateField >= '4-17-2010'

    If this isn't what you are looking for, perhaps you could include sample data and give a sample of what you would like the query to return.

  • October 25, 2009 at 2:20 pm

    #1070453

    Thanks SSC Rookie for replying so quickly. I apologize for not propetly explaining the problem. This job wil be run monthly, starting 12/09/2009 until 03/10/2010. We will need to select data based on one criteria during that date range and another criteria after. Since I don't want to run the job manually each month and set the date range I have to code for all of the dates ranges.

    Here is what I came up with:

    Declare @Last_Day_Prev_Month varchar(8)

    Declare @First_Day_Prev_Month varchar(8)

    Print @Last_Day_Prev_Month

    Print @First_Day_Prev_Month

    IF(Select Datepart(m, convert(varchar(8), Getdate(), 112))) = '12'

    Begin

    SET @First_Day_Prev_Month = '20091211'

    SET @Last_Day_Prev_Month = '20091231'

    END

    IF(Select Datepart(m, convert(varchar(8), Getdate(), 112))) = '01'

    Begin

    SET @First_Day_Prev_Month = '20100101'

    SET @Last_Day_Prev_Month = '20100131'

    END

    IF(Select Datepart(m, convert(varchar(8), Getdate(), 112))) = '02'

    Begin

    SET @First_Day_Prev_Month = '20100201'

    SET @Last_Day_Prev_Month = '20100228'

    END

    IF(Select Datepart(m, convert(varchar(8), Getdate(), 112))) = '03'

    Begin

    SET @First_Day_Prev_Month = '20100301'

    SET @Last_Day_Prev_Month = '20100310'

    END

    IF(Select convert(varchar(8), Getdate(), 112)) > '20100310'

    Begin

    SET @First_Day_Prev_Month =(select convert(varchar(30),dateadd(month,datediff(month,0,getdate())-1,0),112))

    SET @Last_Day_Prev_Month =(convert(varchar(30),dateadd(day,-1,dateadd(month,datediff(month,0,getdate()),0)),112))

    END

    print @First_Day_Prev_Month

    print @Last_Day_Prev_Month

    I know it would be better if I used a CASE statement, but I'm having a little trouble getting my CASE statement to work. I hope this makes sense, If not pls let me know.

    Thanks again

  • October 25, 2009 at 2:33 pm

    #1070455

    I edited and changed the above code, it just might work a little better this way.

  • October 25, 2009 at 3:10 pm

    #1070459

    Hey again Trudye,

    We really need to get you out of this idea that dates have to be converted to strings. 😉 Try this:

    SET @First_Day_Prev_Month =

    CASE

    WHEN getdate() < '20100310' THEN

    CASE

    WHEN DATEPART(month, getdate()) = 12 then '20091211'

    WHEN DATEPART(month, getdate()) = 1 then '20100101'

    WHEN DATEPART(month, getdate()) = 2 then '20100201'

    WHEN DATEPART(month, getdate()) = 3 then '20100301'

    --etc etc

    ELSE NULL

    END

    WHEN getdate() >= '20100310' then convert(varchar(30),dateadd(month,datediff(month,0,getdate())-1,0),112)

    ELSE NULL

    END

    __________________________________________________

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

  • October 25, 2009 at 4:01 pm

    #1070461

    Hey SSCrazy. I'll try and change my dating ways. LOL

    The reason I code dates that way is because the people that are mentoring me code them like that. All of the code in our database use varchar(8).

    Thanks so much for the coding idea, I'll take a look at it and apply as necessary.

  • October 25, 2009 at 4:37 pm

    #1070463

    They are old school, which I can appreciate, but you are just wasting CPU cycles.

    __________________________________________________

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

  • October 25, 2009 at 10:06 pm

    #1070524

    Why not just drop the date ranges into a table and forget the month stuff?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 26, 2009 at 6:41 am

    #1070648

    Thanks Champion for the feedback, worth considering.

  • October 26, 2009 at 6:44 am

    #1070649

    Psssst Trudye.

    "Champion" is Jeff's title. His name is just above it.

    Or shall we start referring to you simply as "SSC Rookie?" 😉

    __________________________________________________

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

  • October 26, 2009 at 1:53 pm

    #1070904

    Sorry Jeff.

    I hope to one day be worthy of the title SSC Rookie

  • November 12, 2009 at 9:50 pm

    #1078700

    trudye10 (10/26/2009)

    Sorry Jeff.

    I hope to one day be worthy of the title SSC Rookie

    Heh... lot's of people make the same mistake. No worries.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

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