CODEWITHSUNDEEP
sqlsql-server
user4731064
user4731064

Reputation: 43

SQL - select data that have dates greater than a month in a year

I have a table called quiz and it has 3 columns:

enterYear  enterMonth  quizMark

2013        7            9.5   
2013        8            8.5    
2013        9            9.75    
2013       10            10    
2013       11            7.75    
2013       12            8.5
2014        1            5   
2014        2            8.75    
2014        3            10

Now, I want to select the entries that were enter after Sept. of 2013 (including Sept). I tried this which was wrong:

select * from quiz q where q.year>=2013 and q.month>=9

This omitted all the entries in 2014 since their months are less than 9, but I do want them in the result since they are after Sept. of 2013.

So then, I tried this

select * from quiz q 
where Convert(date, CAST(q.year as varchar(4))+'/'+CAST(q.month as varchar(2))+'/01')>='2013/9/01'

which showed up the result for like a half second and then quickly disappeared by giving an error message:

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string."

Can someone help with the correct code to achieve the result, very appreciated!

Upvotes: 1

Views: 2355

Answers (3)

Martin Smith
Martin Smith

Reputation: 453897

Depending on your indexes you might find this gives better performance. Expressing as a single range seek with a residual predicate rather than or which often causes a scan.

select * 
from quiz q 
where (q.year>=2013) and (q.month>=9 OR q.year>=2014)

Upvotes: 2

Mat Richardson
Mat Richardson

Reputation: 3606

select * from quiz q where (q.year>=2013 and q.month>=9) OR q.year>=2014

This will capture all your dates from September 2013 upwards.

Upvotes: 2

Kevin
Kevin

Reputation: 7309

select * from quiz q where q.year>2013 or(q.year=2013 and q.month>=9)

Upvotes: 7

Related Questions