PROS! Converting test into an SQL formatted date!

brianb

hello pros,

I'm pulling info from Amazon's XML Feeds on certain products, then parsing the info and INSERTing it into my own MySQL database.

Problem...

Amazon lists their dates as so:
03 December, 2002

I need to convert this into a nice SQl formatted date.

Any ideas on how to do so? Any coding examples are appreciated!

cgraz

DATE_FORMAT is probably the easiest way. http://www.mysql.com/doc/en/Date_and_time_functions.html search the page for DATE_FORMAT

Cgraz

brianb

Hello,

Thanks for the response.

I am aware of MySQL date and time functions, the thing I'm confused with is how to convert something like 17 December, 2002 into a nice MySQL format, then stick it in a date field in my database.

Any ideas?

thanks.

cgraz

ahh, sorry. To create a date in timestamp format, i think you can do

$time = date( 'YmdHis', time() );

But a timestamp has a lot more parameters than what you have: date, month, year, so I dont' know how you'd get a timestamp out of that.

Cgraz

Weedpacket

Well, MySQL should have some functions for converting date strings into and from various MySQL date and time formats. In PHP, to convert a date string into a (Unix-style) timestamp (which would then need either a MySQL function or cgraz's code to convert it into a MySQL timestamp), there's the [man]strtotime[/man] function.

brianb

Ok...

Let me refresh...

I have dates coming in that look lik this:

14 January, 2003

before i can insert this date into my MySQL database, I need to convert that date into this:

2003-01-14

Can anyone here give me a little example code on how to do this. The dates aren't being gerenated from anything, they are coming from a website, so exactly what comes from the website has to go into my DB.

Any help is appreciated.

Weedpacket

[man]strtotime[/man] and [man]date[/man]. That's if you don't want to use MySQL functions at all.

brianb

Thanks Weed...

Just for reference how would I do it if I wanted to use the MySQL method?

brianb

can someone please let me know how to do this? DATE and STRTOTIME are confusing the hell out of me. maybe I should go with a MySQL method?

Again, my dates are coming to me like this:

03 December, 2002

Now, I know how to change the date when it's coming out of my database like so:

2002-12-03 (sits in my database like so)

So..when you pull the date from your database you want it to look a certain way so you do this:
DATE_FORMAT( $the_date, '%e %M, %Y' )

I NEED TO convert:

03 December, 2002

INTO

2002-12-03

...then enter it into my database.

Someone! please help! 🙂

All coding examples are deeply appreciated!!!!

thanks.

Weedpacket

So you want something that goes something like:

$input_date='12 December, 1999';
$output_date = date('Y-m-d', strtotime($input_date));
echo $output_date;

The Right Thing would be for that to work, of course, but English is a bit fuzzier than strtotime() can cope with. The problem is the ,, of all things.

$input_date=str_replace(',', '', '12 December, 1999');
$output_date = date('Y-m-d', strtotime($input_date));
echo $output_date;

where, needless to say, '12 December, 1999' is to be replaced with the real source of the date.

brianb

Thanks Weed.

Actually...with this code:

$date = str_replace(',','',$grabBag['release']);
$release = date('Y-m-d', strtotime($date));

My result everytime is this:

2003-01-03

Any idea what's happening there?

Weedpacket

Check the value of $grabBag['release'] (by echoing it). If it's 0 or null or something like that, strtotime() will interpret that as meaning "now".

brianb

hey Weed,

Sorry bout that. I had an error in my code. You were right! Anyway, thanks for the help! Works like a charm!

milind24

change your variablename date withg other name it will work ,
it is taking current date

$date = str_replace(',','',$grabBag['release']);
$release = date('Y-m-d', strtotime($date));

change as

$Adate = str_replace(',','',$grabBag['release']);
$release = date('Y-m-d', strtotime($Adate));