Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Unanswered: Searching Dates in Query Analyzer

    Hello Gang,

    I have a strange problem that I haven't dealt with before.

    I need to execute a piece of code based on date ranges. If the date range is:

    Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
    -----------------------------------------------------------
    Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y


    I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.

    [1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or

    [2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>

    In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.

    How do I achieve both # 1 & 2 above in a code? Does this explain better.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT PatP
    ,  CASE
          WHEN Convert(CHAR(5), PatP, 1) BETWEEN '02/01' AND '07/31' THEN 'ABC'
          ELSE 'PQR'
       END
       FROM (
    SELECT DateAdd(day, d2 + d1 + d0 + DateDiff(day
    ,     '1753-01-01', GetDate()), '1753-01-01') AS PatP
       FROM (SELECT 100 * number AS d2 FROM master.dbo.spt_values
          WHERE 'L' = type AND number BETWEEN 0 AND 3) AS z1
       CROSS JOIN (SELECT 10 * number AS d1 FROM master.dbo.spt_values
          WHERE 'L' = type AND number BETWEEN 0 AND 9) AS z2
       CROSS JOIN (SELECT number AS d0 FROM master.dbo.spt_values
          WHERE 'L' = type AND number BETWEEN 0 AND 9) AS z0
       ) AS r937
    ORDER BY r937.PatP
    -PatP
    Last edited by Pat Phelan; 12-12-07 at 00:33.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What happens if the guy runs the report on Valentine's day (Which falls into neither category)?

  4. #4
    Join Date
    Dec 2007
    Posts
    3
    Hmmm . . . any ideas on the Valentines day question?

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    What do you mean by
    Hmmm . . . any ideas on the Valentines day question?
    Duh!
    You are the one that set the initial conditions!

    Quote Originally Posted by JJoshi
    I need to execute a piece of code based on date ranges. If the date range is:

    Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
    -----------------------------------------------------------
    Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
    August of 2008 and January of 2009, display <PQR>.
    Don't you know what you want to do with the month of February between 01 Feb and 27 Feb? And once you do, you have the code provided above to attempt to code it yourself ... be resourceful and give it a shot!

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •