Changing the format of the date

[McMaster]

Hey,

 

I have a database with a date field that has the date in this format:

 

August 2, 2010 11:04 pm

 

I was wondering if it was possible to change this so I have the day, month and year in separate variables like this:

 

$day = "2";

$month = "8";

$year = "2010";

 

Also notice how I have changed August to "8". I was wondering if there was an easy way around getting this done?

 

Thanks in advance

[Vikas Jayna]

You can convert the above string into date format by using the below query:-

 

select str_to_date('August 2, 2010 11:04 pm','%M %e, %Y %h:%i %p');

 

output is 2010-08-02 23:04:00

 

 

Now you can apply other date functions on this to get the desired result. For eg.

 

select Month(str_to_date('August 2, 2010 11:04 pm','%M %e, %Y %h:%i %p')), day(str_to_date('August 2, 2010 11:04 pm','%M %e, %Y %h:%i %p')), year(str_to_date('August 2, 2010 11:04 pm','%M %e, %Y %h:%i %p'))

 

This will give the desired output of month=8, day=2 and year=2010

[McMaster]

Thanks for your response. The thing is, the date is contained within 1 table field only. So Would I do something like:

 

$sql = mysql_query = ("select Month(str_to_date(date,'%M %e, %Y %h:%i %p')), day(str_to_date(date,'%M %e, %Y %h:%i %p')), year(str_to_date(date,'%M %e, %Y %h:%i %p')) from recent_activity");

 

Also, this may sound silly but how exactly do I extract the month, day and year from that query? Would I just use something like mysql_result($sql)?

 

Thanks

[PFMaBiSmAd]

You would better off using a correct DATE or DATETIME data type to store your information in the table (that's what the DATE or DATETIME data types are for.)

 

The second query that Vikas Jayna posted does get the three pieces of information you asked about, using the mysql MONTH(), DAY(), and YEAR() functions in the query. I would use alias names for each part so that it is simpler to reference them when you retrieve the data in your php code.

[McMaster]

Yeah I see what you are saying. Hmm, so how would I go about extracting those MONTH(), DAY(), and YEAR() functions from the query and echoing them on to the page?