Help with inserting date into mysql db

[rayarman]

Hi,

I've got the following type of date string parsed from an OFX file:

20090902000000[-5:EST]

How can I get this into my mysql db via php - formatted as d-m-Y

I've tried :

$ofxdate = strtotime("20090902000000[-5:EST]");
			  
			   
			  $stmtdate = date("d-m-Y",$ofxdate);

Obviously wrong! ... Any assistance appreciated!

How would I do this?

[melissagirl]

Why don't you try something like:

date_parse_from_format("%Y%m%d%H%i%s[%z:%Z]", $ofxdate);

[rayarman]

I'm working on a shared server with php 5.2.10... unfortunately the date_parse_from_format function isn't available below 5.3

any alternative to that function?

[melissagirl]

This should work for that version and much older versions too. I just used preg_replace to change that format into a m-d-Y format. You can change the "replace" portion from '$2-$3-$1' to whatever you need if that doesn't suit you.

$ofxdate = strtotime(preg_replace('/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})\[(-?\d{1,2}):([A-Z]{3})\]/', '$2-$3-$1', '20090902000000[-5:EST]'));

[rayarman]

Thanks Melissagirl!

I've tried a long winded version of my own (i'm new to coding in general).... some dates may be in the "20090902000000[-5:EST]"
format, so may just be "20090902".

//find occurence of [ within the stmtdate

if (strpos($stmtdate,"[") > 0){
				
$zonestart = strpos($stmtdate,"["); //start position of timezone
				
$stmtdate = substr($stmtdate,0,$zonestart);
				
$stmtdate = date("d-m-Y",strtotime($stmtdate));
									   
}
else
{
$stmtdate = date("d-m-Y",strtotime($stmtdate));
}

[melissagirl]

Ok, try this then. It will try the long format first, then the short format. I have it outputting directly to d-m-Y formatted string, although that may need to be adjusted if the date you gave me was Feb 9, rather than Sept 2.

$stmtdate = '20090902000000[-5:EST]';
$ofxdate = preg_replace('/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})\[(-?\d{1,2}):([A-Z]{3})\]/', '$3-$2-$1', $stmtdate);
if ($ofxdate == $stmtdate) {
    $ofxdate = preg_replace('/(\d{4})(\d{2})(\d{2})/', '$3-$2-$1', $stmtdate);
}

[rayarman]

for '20090902000000[-5:EST]' , it gave me back 13-12-1901

[rayarman]

Ok, try this then. It will try the long format first, then the short format. I have it outputting directly to d-m-Y formatted string, although that may need to be adjusted if the date you gave me was Feb 9, rather than Sept 2.

$stmtdate = '20090902000000[-5:EST]';
$ofxdate = preg_replace('/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})\[(-?\d{1,2}):([A-Z]{3})\]/', '$3-$2-$1', $stmtdate);
if ($ofxdate == $stmtdate) {
    $ofxdate = preg_replace('/(\d{4})(\d{2})(\d{2})/', '$3-$2-$1', $stmtdate);
}

Whoops! My mistake...works perfectly, thanks again!

The time zone cannot be a + can it? as well as - ?

[melissagirl]

for '20090902000000[-5:EST]' , it gave me back 13-12-1901

That's odd. You may want to double-check what you uploaded. I just uploaded to my webserver and it shows the correct value.

[melissagirl]

Sure, just use this:
'/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})\[([-|\+]?\d{1,2}):([A-Z]{3})\]/'