Date conversion problem

[sickpuppy]

Hi

I am downloading an XML newsfeed into my MySql db and am having difficulty re-formatting the date fornmeach news item into the correct format for mysql (yyyy-mm-dd). It is being output in the feed as dd/mm/yyyy.

Using date_format('" & sDate & "', '%d-%m-%Y') within the
SQLString="INSERT INTO....." block only converts dates to '2020-01-20'

Please can you advise as to how to convert the date output into mysql date format for my INSERT statement.

This is driving me nuts!

 

[hos2]

perhaps

date_format('$sDate', '%Y-%m-%d')

you convert the date whatever the format is to the format that you want . and sDate is not a variable declartions ???

 

[sickpuppy]

Unfortunately date_format('$sDate', '%Y-%m-%d') is converting todays date (07-01-2004) to 2007-01-20.

Any ideas?

 

[r937]

that's because date_format() expects its parameter to be an actual date

if you feed it the string '07-01-2004' then it first has to convert that string to an actual date value

there's a good explanation for the way mysql handles date strings on the mysql.com site

bottom line, you must format your date strings into year/month/day sequence

use subtring() to pull out the year, month, day portions of the incoming string

then use concat() to put them back together into yyyy-mm-dd format







rudy

http://r937.com/

SQL Consulting