Date format data is coming in M/D/YYYY format(for day and month value less than 10) from the source csv files.
We want to load it into Teradata DW.
Could you help in any INMOD function that converts the date to DD/MM/YYYY format.
We are having a fastload script to load into stage , but that is failing with invalid date format because of above issue.
Thanks in Advance!
You don't need an INMOD to do this (although using one will do what you want).
An alternative approach:
- load the data 'as is' into a staging table and define this column as VARCHAR(10).
- when you read from that staging table, convert it to a date column using the following (assume COL2 is the column in question).
CAST( (STRTOK(col2,'/',3)||'-'||RIGHT('0'||STRTOK(col2,'/',2),2)||'-'||RIGHT('0'||STRTOK(col2,'/',1),2)) AS DATE)
This caters for both 1 and 2 digit months and days. It assumes that the year is always a 4 digit value.
If you don't want to do this when reading the staging table you can always use it in an UPDATE and use it to set the value in a new (DATE) column. Up to you.