Text date not importing properly

[cnpja]

Hi there,

I am using Access 2003, and am trying to import a csv into a table. I have a date field (Orig Date) from the csv that I need stored in the format of yyyy-mm-dd (for Crystal reports), but the csv has the date in the format of dd-mm-yyyy.
I try to import the csv into the table but I get a "Type Conversion Failure" on the Orig Date field.
I then changed the Access field type to text, and imported it which worked with no errors, but it still stores the date as dd-mm-yyyy. I changed the date field to Date/Time and put a format of yyyy-mm-dd on it, but because the date is september 3, 2008 (2008-09-03) access seems not to know if the 3 or the 9 is the day or month, and it formats it as 2008-03-09 which is wrong.
Is there any to fix this?

Thanks!

 

[PHV]

So, you have a text field with data formatted as 'dd-mm-yyyy' ?
SELECT Right(OrigDate,4) & Mid(OrigDate,3,4) & Left(OrigDate,2) AS CrystalDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cnpja]

Thank you!

The reason I am needing a fix is that the server where the csv originates from made a change recently to the OS and it now has this dd-mm-yyyy format while before it was yyyy-mm-dd.
The tables that I import the data into alreay have lots of records but with the good format (yyyy-mm-dd). so I don't think that 'select' would work as I have existing data that would be messed up.
Maybe if I create a temp table, import the csv into it, modify the date, then export it into a new csv, which I then import into the good table....yikes.

CNPJA

 

[PHV]

UPDATE yourTable
SET OrigDate = Right(OrigDate,4) & Mid(OrigDate,3,4) & Left(OrigDate,2)
WHERE OrigDate Like '##-##-####'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cnpja]

Great!

Thats an excellent idea.

I'll give it a try!

CNPJA