SELECT date range with year and month fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT date range with year and month fields

Author

Topic

Ade
Starting Member

9 Posts

Posted - 2013-06-13 : 10:32:26

Hi

I want to select data between two date ranges where the date values are held in separate year and month columns. My current query fails because it excludes (2011,12) and (2012,1).

Any help would be greatly appreciated.

Thanks

SELECT * FROM tbDates
WHERE (year >= '2011' and month >= '2')
AND (year <= '2012' AND month <= '11')

--Create the test data.

create table tbDates (year int, month int)

insert into tbdates values
('2011','1'),
('2011','2'),
('2011','3'),
('2011','4'),
('2011','5'),
('2011','6'),
('2011','7'),
('2011','8'),
('2011','9'),
('2011','10'),
('2011','11'),
('2011','12'),
('2012','1'),
('2012','2'),
('2012','3'),
('2012','4'),
('2012','5'),
('2012','6'),
('2012','7'),
('2012','8'),
('2012','9'),
('2012','10'),
('2012','11'),
('2012','12')

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-13 : 10:41:02

If you are on SQL 2012 use this:


WHERE
   DATEFROMPARTS(year,month,1) >= '20110201'
   AND DATEFROMPARTS(year,month,1) <= '20121101'

If you are on an earlier version of SQL, replace the left side of each comparison operator with dateadd(mm,month-1,dateadd(yy,year-1900,0))

Ade
Starting Member

9 Posts

Posted - 2013-06-13 : 10:56:33

Thanks James

Yes that works and solves my problem! Very grateful.

Regards

Adrian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources