| Author |
Topic |
|
noyellatmonkeys
Starting Member
15 Posts |
 Posted - 2010-01-21 : 17:15:30
|
Hey all,Ive been trying to figure this out for a while with no luck. when i try to run the following query: select firstname, lastname, email, birthday from signups where (birthday > '1/21/1955' AND birthday < '1/22/1985') AND ( country = 'US' OR country = 'canada' ) And ( ethnicity = '1' OR ethnicity = '3' OR ethnicity = '5' OR ethnicity = '6' OR ethnicity = '7' ) And doubleoptin = 1 and unsubscribe = 0 and bademail = 0 it pulls only the signups that land on either 1/21 or 1/22 and between the year range. I also tried this same thing with "between" but got the same result (two to be exact which have birthdays of 1/22/1969 and 1/21/1960).I'm feeling baffled by this as i havent seen this happen before when ive done this.any ideas?Thanks!!! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-21 : 17:21:56
|
Look at the records that you expected to be in the resultset.They must have a birthday or country or ethnicity or doubleoptin or unsubscribe or bademail that isn't fitting your where clause.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
noyellatmonkeys
Starting Member
15 Posts |
Posted - 2010-01-21 : 17:34:10
|
| Yeah i looked at that, if i remove the birthday part entirely from the query i got 700+ results. When i spot checked these there were at least 5 right of the bat that i saw that should have been returned. again it seems really strange that when the birthday piece is there the only two results that return have the same month and date as is specified in the query. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-21 : 17:41:57
|
If your birthdaycolumn is a datetime then give this a try(birthday > '19550121' AND birthday < '19850122')
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-21 : 17:44:04
|
And only to test, if it isn't a datetime:(convert(datetime,birthday) > '19550121' AND convert(datetime,birthday) < '19850122')
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-21 : 18:29:36
|
(convert(datetime,birthday) >= '19550121' AND convert(datetime,birthday) < '19850122')
N 56°04'39.26"E 12°55'05.63" |
|
|
|
noyellatmonkeys
Starting Member
15 Posts |
Posted - 2010-01-21 : 18:40:25
|
| ok i tried to do the convert thing... but i got this errorMsg 241, Level 16, State 1, Line 1Conversion failed when converting datetime from character string.when i looked at the column type it is varchar |
|
|
|
noyellatmonkeys
Starting Member
15 Posts |
Posted - 2010-01-21 : 19:05:34
|
| Ok I figured this out... i was getting the error cause i had some ill formatted dates in my column and now the convert is working... Thanks everyone!!! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-22 : 02:09:24
|
Welcome  If it is possible then you should make your columns with a datevalue a datetime datatype.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
Where Date = "what the heck is happening"