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 between 27/05 to 03/06 i will get the rows of id 3&4.
how to do it?
hi
i tried to do this ( i had to make a small change )
between 27/05 to 03/06 to between 0527 to 0603
i am comparing month first and then day
to do this u have to compare in number format monthday
thats why 27/05 becomes 0527 and 03/06 becomes 0603
hope it helps
/***************************************************************************/
-- drop all temp tables
DECLARE @SQL NVARCHAR(max) = (SELECT 'DROP TABLE ' + Stuff( ( SELECT ',' + NAME
FROM
tempdb.sys.tables FOR
xml path(
'') ), 1, 1, ''));
--PRINT @SQL;
EXEC Sp_executesql
@SQL;
/***************************************************************************/
-- create tables
-- INT VARCHAR(100) DATE DATETIME DECIMAL(10,2)
create table #tablename
(
id int,
name VARCHAR(100),
dob DATETIME
)
go
/***************************************************************************/
-- insert data into tables
insert into #tablename select 1 ,'ABC','1974-01-01'
insert into #tablename select 2 ,'BDS','1984-12-31'
insert into #tablename select 3 ,'QWE','1943-05-27'
insert into #tablename select 4 ,'BDX','1987-06-03'
go
/****************************************************************************/
-- select from all temp tables
DECLARE @SQL1 NVARCHAR(max) = (SELECT Stuff((SELECT '; select * from ' + NAME +
' go '
FROM tempdb.sys.tables
FOR xml path('')), 1, 1, ''));
--PRINT @SQL1;
EXEC Sp_executesql
@SQL1;
/***************************************************************************/
SELECT *
FROM #tablename
WHERE
Cast(Cast(Format(Datepart(mm, dob), '0#') AS VARCHAR)
+ Cast(Format(Datepart(dd, dob), '0#') AS VARCHAR) AS INT)
BETWEEN 0527 AND 0603
WHERE RIGHT(CONVERT(varchar(8), DOB, 112), 4) BETWEEN '0527' AND '0603'
did this thanks
