Date format

UDA
Enthusiast

Date format

[font=Tahoma]Hi,
I am getting a file which has a date value. But if the months or days are less than 10, then the date format will be M/D/YYYY (1/1/2008). If it is greater than 10, then the format will be MM/DD/YYYY (10/10/2008). I am trying to import the file data to a Teradata table. But the insert query fails which shows that Teradata cannot import the date with format M/D/YYYY.ie,6/19/2008 is not acceptable. It accepts only 06/19/2008.

Could any one give some solution which I could load data without modifying the values in file?

Thanks.[/font]
5 REPLIES
Enthusiast

Re: Date format

Which is the utility you are using Fload or Mload.
Hows your data.comma separated?
Teradata Employee

Re: Date format

Teradata does not have FORMATs that permit single-digit month or day.

You can use INMOD or AXSMOD to reformat the input records "on the fly" to be acceptable to Teradata.
Or you can load as VARCHAR and use string functions (or UDFs) within the database to add leading zeros and/'or do the conversion.
Enthusiast

Re: Date format

Thanks Fred...Seems that's the only option available...
Enthusiast

Re: Date format

Thanks Fred...Seems that's the only option available...
Enthusiast

Re: Date format

Another solution is to load the date column as a varchar and then use the following SQL to convert the varchar date to a real date and insert itno the table with the column defined as DATE. The following SQL will convert date values  1/1/2001, 01/1/2001, 1/01/2001 to 01/01/2001.

CAST(

  CASE

WHEN  substr(invce_prcs_dte   ,3,1)      =  '/'   and  substr(invce_prcs_dte   ,5,1) = '/'  THEN SUBSTR (invce_prcs_dte   ,1,3) || '0' || substr(invce_prcs_dte   ,4,7)

  WHEN  substr(invce_prcs_dte   ,2,1)       = '/'   and  substr(invce_prcs_dte   ,4,1) = '/'  THEN '0' || SUBSTR (invce_prcs_dte   ,1,2) || '0' || substr(invce_prcs_dte   ,3,7)

 WHEN  substr(invce_prcs_dte   ,2,1)       = '/'   and  substr(invce_prcs_dte   ,4,1) <>  '/'  THEN '0' || SUBSTR (invce_prcs_dte   ,1,9)

  WHEN  substr(invce_prcs_dte   ,2,1)       <>  '/'   and  substr(invce_prcs_dte   ,4,1) =   '/'  THEN   SUBSTR (invce_prcs_dte   ,1,3)  || '0' || substr(invce_prcs_dte   ,4,6)

ELSE     substr(invce_prcs_dte   ,1,10)   end 

as date format 'mm/dd/yyyy' )   ,