date formatting

dan7474

I am working on some financial programming and I need to get some dates converted from like 14-Jan-05 to something like 2005-01-14.

I want to pull the historical data off yahoo in csv format and put it into a database, however I can't then make the selects I want in the format yahoo provides (14-Jan-05)

is there a good way, either in the csv file or the query to change the dates to the format I want? I tried something like

$result = mysql_query("SELECT * FROM yahootemp WHERE date_format(Date,\"%Y-%d-%m\") > 2005-01-01")

which didn't work. The csv data looks like this before I insert it into mysql...

Date,Open,High,Low,Close,Volume,Adj. Close*
14-Jan-05,9.09,9.34,8.96,8.96,237400,8.96

any help is appreciated!!

bodzan

strtotime()

I'm pretty sure that it can convert the data input you have into the date format which you can format to your needs by using date() function...

Weedpacket

Failing that, two digits followed by a hyphen followed by three letters followed by a hyphen followed by two digits is a pretty trivial thing to parse by hand. Using explode() on '-' to get the parts, then reassembling them into "2005 Jan 14" (which strtotime certainly recognises) wouldn't be difficult. Of course, you could also use an array that maps "Jan"=>"01", "Feb"=>"02" and convert that part to digits yourself. Then you've got "2005 01 14". And if you can do that you can use hyphens instead of spaces when you're reassembling and then you've got "2005-01-14", and then you can ... oh, wait. That's what you wanted.

dan7474

thanks for the replies, and I have gotten the date() function to reformat a date field by itself, I'm just having trouble doing so to the entire csv file which has anywhere between 5 and 300 lines. I think I found an example that might work using strtotime, but I need to pull just the dates out of the file as an array....is there a way to pull them out, run the code to convert the format, then put them back in? Or would it be easier to just import them seperately into mysql.

I'm also still learning (a newbie, really) so I appreciate the guidance.

bodzan

As you can se here file() function reads entire file to an array so you will have for each line one element of an array so you can perform the date conversion on each element...