Welcome to WebmasterWorld Guest from 54.145.53.251

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Searching thru dates

dates, mysql

     
5:40 pm on Nov 11, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Oct 2, 2006
posts:187
votes: 0


I have a date column in a table that displays the date for a given record like this "November 10, 2008". What would my sql statement look like if I just wanted to find all of the records from "November".

WHERE Date = "November" doesn't work for me, I get no records found. I need to look past the day and year.

6:06 pm on Nov 11, 2008 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15755
votes: 0


you would need to use LIKE

WHERE Date LIKE "November%"

the % represents 'any number of characters'

6:28 pm on Nov 11, 2008 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


It would be better if you can change the field type (and underlying data) to DATETIME instead of VARCHAR (or at least add another field of type DATETIME). Using VARCHAR fields to store dates is just asking for problems later on.
12:55 pm on Nov 14, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Oct 2, 2006
posts:187
votes: 0


Thanks jatar_k

I did set it up as a VARCHAR field, this is actually one of the first databases I've put together. What would it take to change the field type (and underlying data) to DATETIME?

4:11 pm on Nov 14, 2008 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


Most likely- add another field of DATETIME format, then do a conversion of the "Date" field, then drop the "Date" field after all the values have been converted.

One other suggestion I forgot to mention before, using reserved words (like "Date" or "Time") for field or table names is not a good idea. It often causes major headaches down the road.