Date comparison - but only Month and Day?

[gbaughma]

I need to write a query off of a table that looks like this:

SKU StartDate EndDate
1234 5/1/2010 5/15/2010

... so that it finds all records based on today's date which is between StartDate and EndDate.

BUT... I also have to ignore the year....

In other words, if today is 5/2/2011, since that is between 5/1 and 5/15, it should find the record with SKU 1234.

... or is there another way to do a date field with JUST the month and day?

So, in other other words (God, this is such a bad habit, explaining everything at LEAST twice so I make sure my listener gets it.... <sigh>)

Every year, starting on 5/1, and continuing until 5/15, my query should hit that record.

Any thoughts?


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[RiverGuy]

Do you have a Date table in your database? It's a common table which typically has one row for each date between x and y dates. There are typically an array of other columns for things such as Month, Date, Day of Month, Year, Quarter, Day of Week, Is Holiday, etc. Oftentimes, you will have columns to include both numerics and descriptions. So you might have a row with Day of Week of 1 and Day of Week Description of "Sunday."

If you utilize a table like this, you'll have a much easier time constructing queries to produce results as you've described above. All it would involve would be a join to the date table with a month of 5 and day of month between 1 and 15.

 

[gbaughma]

Mmm.... it looks like something like this might work?

select *
from t
where datepart(month,t.fieldA) >= datepart(month,t.fieldB)
      or (datepart(month,t.fieldA) = datepart(month,t.fieldB)
            and datepart(day,t.fieldA) >= datepart(day,t.fieldB))

Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[donutman]

Is there no concern for the possibility that the date range spans January 1?
Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gbaughma]

You know, as I was putting in the dates into the table, I realized that that could be an issue.

I have to think about it just a touch more.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Well, I know the logic, now I just have to get the syntax.

Essentially, I have to say (in tsql, of course)

SELECT the SKU from the table, but only if the following conditions are true:
If the StartDate < EndDate then
the month and date should be INSIDE the start and end dates
ELSE if StartDate > EndDate then
the month and date should be OUTSIDE the start and end dates.

I thought I could use a CASE in a WHERE, but not DIRECTLY in a where.... for example

SELECT SKU INTO #tmp_table FROM t
WHERE CASE
t.StartDate < EndDate THEN
bla bla conversion stuff
ELSE
t.StartDate > t.EndDate Then
bla bla selection stuff in the other direction.

.... Unfortunately, SQL doesn't like the CASE statement used that way.

So, option 2 would be to loop through the entire recordset using IF's, but that's messy... and I'm not even sure how I would go about that in a SP.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com