Date Format Correction Using INMOD function

Database
Enthusiast

Date Format Correction Using INMOD function

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!

1 REPLY
Apprentice

Re: Date Format Correction Using INMOD function

Hi,

 

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.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com