...

View Full Version : Date format issue



JanObe
08-09-2006, 12:15 AM
Hi, well my boss said the format for the date in our database is mm/dd/yyyy and when i get the current_date() to compare the two I have the problem of them being different

current_date() is yyyy-mm-dd (right?)
event_startDate is mm-dd-yyyy

I tried using the get_format() function to get them to be the same but it doesnt seem to be working correctly.

What I am trying to do is write a query that gets the information for the next upcoming meeting. Thus, the events_startDates needs to be greater than the current_date() and i merely grab the "closest" meeting or the first row as it were.

This is what i have so far. How do I get the dates to be comparable to each other?

$query = mysql_query("SELECT * FROM meeting_list, events WHERE event_startDate > current_date() AND eventID = ID AND event_type != 'Sales' ORDER BY event_dates ASC LIMIT 1");

Fumigator
08-09-2006, 12:41 AM
You should always store dates using a data type of "date". Find out if this is the case.

If your event_startDate is being stored as a string, you'll need to use the str_to_date() function on it to transform it into a date before you can use comparison operators on it.

JanObe
08-09-2006, 12:55 AM
lol...yeah he just got off the phone and told me it is a string. He thinks the best way to handle this is to split the string up and compare each part.

personally, I dont know what would be best or easiest but whatever i choose it is only temporary. He plans on changing the column to date soon.

bah, this is silly

Fumigator
08-09-2006, 01:05 AM
Splitting up the string and comparing each part would be the absolute worst way to do it... try str_to_date() first.

guelphdad
08-09-2006, 03:27 AM
See the link in my signature about varchar dates.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum