SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Where Date = "what the heck is happening"
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

noyellatmonkeys
Starting Member

15 Posts

Posted - 01/21/2010 :  17:15:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 01/21/2010 :  17:21:56  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

noyellatmonkeys
Starting Member

15 Posts

Posted - 01/21/2010 :  17:34:10  Show Profile  Reply with Quote
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.

Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 01/21/2010 :  17:41:57  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 01/21/2010 :  17:44:04  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30101 Posts

Posted - 01/21/2010 :  18:29:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
(convert(datetime,birthday) >= '19550121' AND convert(datetime,birthday) < '19850122')


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

noyellatmonkeys
Starting Member

15 Posts

Posted - 01/21/2010 :  18:40:25  Show Profile  Reply with Quote
ok i tried to do the convert thing... but i got this error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

when i looked at the column type it is varchar
Go to Top of Page

noyellatmonkeys
Starting Member

15 Posts

Posted - 01/21/2010 :  19:05:34  Show Profile  Reply with Quote
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!!!
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 01/22/2010 :  02:09:24  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000