Access 97 to Teradata Invalid Date Format 1

[ddvmor]

Hi,
Not sure if I should be posting this in a teradata or an access forum, but here goes:

I'm trying to export the results of a query from Access 97 into a table on teradata. Three of the access fields are date format (dd/mm/yy) going into date fields in teradata.

Problem is... when running the query from Access, i get a helpful error message that simply says 'Overflow'. If I export into a text file and try to upload that into teradata (which I don't really want to do as I'm trying to automate the transfer of data) I gate an 'Invalid Date Format' error.

Is there something I should know about date formats between access and teradata???

Any suggestions would be gratefully received!

Darren.

 

[dnoeth]

Hi Darren,
i can't help you with access, but in Teradata you can specify a format for a date column which is used when a string is converted to a date, e.g.
select '11/03/05' (date, format 'dd/mm/yy')

Depending on a setting in dbscontrol (made by your DBA) this may return 2005 or 1905 as year.

And maybe it's an issue with Teradata ODBC settings:
Options -> "DateTime Format" should probaby be set to "AAA"

Dieter

 

[ddvmor]

Hi Deiter.

You are obviously a genius. The problem lay in the formatting of the date fields in my table, which default to a dd/mm/yyyy format.

I was able to reformat these fields by recreating the table and defining the dates: opdate date format 'dd/mm/yy'. The upload was fine!

Gotta confess, I didn't even know that you could do this until today - you learn something new every day!

Thanks again!