dates with slashes before going into SQL DB

0o0o0

I have cvs file that goes into main db... then from main db it goes to temp db... which I have successfully removing duplicates...

But I am now sorting by latest date cause its not taking the newer of two entries and taking the older one.. i tried min and max.. niether works. I think its the way the date is coming into my db..( shown below) ..

so call it latestdate column.

in those columns are dates written like this.. 10/08/2008

I cant get

$sql = "INSERT INTO XTMP SELECT DISTINCT  ???CODE MIN(latestdate) as latest date     etc etc

to work it properly..

I was thinking and trying stripslashes and convert

just cannot come up with the proper command.

so the date above wouldnt be a date anymore.. ( doesnt matter at this point)
bt would be a number 10082008

which is higher then last months date 9082008

it would be night to do it proper and spin the year to the other end... but at this point im tired of the whole project lol.

Any help would be greatly apprieciated. Its the very last detail of my project 3rd night all night on it, its friday night man! lol .... im comin for help.

thanks.

Piranha

You should really use a datatype for dates, for example date or datetime. Then the database handles all this stuff.

0o0o0

what do you mean by that?

the csv coming in is from an outside source.

Are you saying "they" should use it?

or can I achieve something out of the lousy 10/08/2008 <---

use of dates? sometimes they dont even have the leading zeros!!

if your saying the csv file is not well setup.

my alternative is.. adding my own time stamp into my db on each of the csv rows as they come in.

But that also is a great feat for me apparently I spent the last 3 hours trying to make that work..

but the csv has less columns.. then my db now the timestamp column is added.

im currently lost in spagetti string world here.

so if I were to go into my db now.. goto latestdate column and set it as date or datetime... it would read the slashed date properly?

Piranha

I am not saying how the csv file should look like. Or how you should import it in your database. To be honest I have never imported csv files into databases, I have no idea what you can and what you can't do with it.

What I am saying is that you should use the date or datetime format in the database, the column format seems to be your biggest problem right now.

0o0o0

tried it with date...

just made all my entries 0000-00-00

so um yep.. dbs busted lol.

then did update.. and nope still all 0000-00-00

igorlopez

I'll second Piranhas point, set up the field in DB to be a DATETIME format.

Then populate the db from your csv by converting the csv entry to php's standard timestamp format ($phpdate in the example below) and use this construction to create a matching field data

$mysqldate = date( 'Y-m-d H:i:s', $phpdate );

which then can be used internally in MySQL for sorting.
You can also populate the $mysqldate variable manually by proper mapping between your csv field and the $mysqldate format, i.e yyyy-mm-dd hh:mm:ss if you don't need to perform operations on the date in php.