SQLTeam.com | Weblogs | Forums

Get all rows based on Day and month only

i want to get all data between a range of DAY/MONTH to DAY/MONTH , but no matter what is the year
for example

ID     NAME     DOB
1      ABC      1974-01-01
2      BDS      1984-12-31
3      QWE      1943-05-27
4      BDX      1987-06-03

hi

looks like this question has been posted before ...
anyhow here's the answer

posted before ....

drop create data ...
-- select database tempdb 
USE TEMPDB
GO 

-- drop all tables tempdb 
DECLARE @sql NVARCHAR(max)='';SELECT @sql += ' Drop table ' +  QUOTENAME(TABLE_NAME) + '; ' FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb'; Exec sp_executesql @sql

create table #data
(
ID int,    
NAME   varchar(100),  
DOB date
)
go

insert into #data select 1  ,'ABC', '1974-01-01'
insert into #data select 2  ,'BDS', '1984-12-31'
insert into #data select 3  ,'QWE', '1943-05-27'
insert into #data select 4  ,'BDX', '1987-06-03'
go
							


-- select all tables tempdb 
DECLARE @sql NVARCHAR(max)='';SELECT @sql += ' SELECT * FROM  ' +  QUOTENAME(TABLE_NAME) + '; ' FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb'; Exec sp_executesql @sql
SQL ..
SELECT * 
FROM #data 
WHERE RIGHT( CONVERT(VARCHAR (8), dob, 112), 4) BETWEEN '01/03' AND '02/09'