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
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
-- 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
SELECT *
FROM #data
WHERE RIGHT( CONVERT(VARCHAR (8), dob, 112), 4) BETWEEN '01/03' AND '02/09'