# Thread: Searching Dates in Query Analyzer

1. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,863
What happens if the guy runs the report on Valentine's day (Which falls into neither category)?

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

5. 9th inning DBA
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!

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!

#### Posting Permissions

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