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

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
:slight_smile: :slight_smile:

drop create data ....
/***************************************************************************/


-- 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; 

/***************************************************************************/
SQL ...
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'