Welcome to WebmasterWorld Guest from 54.145.246.183

Forum Moderators: coopster & jatar k

Searching thru dates

dates, mysql

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

5+ Year Member



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)

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



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)

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



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)

5+ Year Member



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)

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



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.

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month