CODEWITHSUNDEEP
sqlsql-serverdateformatting
jenny
jenny

Reputation: 811

How to format a wildcard query for dates in MS SQL?

I have a certain day range, let's say 8-01 to 8-08. I also have a database that has historical data from 1975-2014. Dates are formatted in yyyy-mm-dd form. So for example, today is 2014-08-06. How can I get historical records from 8-01 to 8-08 for all years? Keep in mind my date range might not span nicely across the same month. Another example would be 7/31 to 8/07.

Basically, I want to be able to do BETWEEN(%-8-01 AND %-8-08) where % is a wildcard. However, wildcards seem incompatible with date objects in MS SQL. Do I need to convert the dates to strings? What is the most efficient way of getting a generic month-day range independent of year?

Thanks.

Upvotes: 1

Views: 7655

Answers (4)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

This is a different approach than my other answer. It will make all dates have a year of 1900. Then you only need to take whatever is between your selected dates.

SELECT *
FROM   Table1
WHERE  DATEADD
       (
           year,
           -DATEDIFF(year,'19000101',dateField),
           dateField
       ) BETWEEN'19000801' AND'19000818'

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

DECLARE @StartMonth INT
DECLARE @Month INT
DECLARE @EndMonth INT
DECLARE @StartDay INT
DECLARE @Day INT
DECLARE @EndDay INT

SET @StartMonth = 8
SET @EndMonth = 8
SET @StartDay = 1
SET @EndDay = 8

IF @EndMonth > @StartMonth
BEGIN  
    @Month = @EndMonth 
    @StartMonth = @EndMonth
    @EndMonth= @Month 
END
IF @EndDay > @StartDay 
BEGIN  
    @Day= @EndDay 
    @StartDay = @EndDay 
    @EndDay = @Day
END

SELECT *
FROM   TABLE
WHERE  MONTH(dateField) BETWEEN @StartMonthAND @EndMonth 
       AND DAY(dateField) BETWEEN @StartDay AND @EndDay

Upvotes: 0

Andrew
Andrew

Reputation: 8703

You can extract the month and date part and compare those:

where
month(<yourdate>) = 8
and day(<yourdate>) between 1 and 8

Be aware that if you have an index on this column, you won't be using it this way.

Upvotes: 1

durbnpoisn
durbnpoisn

Reputation: 4669

The wildcard operator cannot be used in a BETWEEN statement. It's really only for LIKE statements. If you want to be able to modify your year, and that's it, you should pass it in as a parameter. The BETWEEN statement will take that with no problem.

Upvotes: 0

Related Questions