All Forums UDA
ashija 10 posts Joined 01/07
19 Jun 2008
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]

shubh 16 posts Joined 09/07
26 Jun 2008

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

Regards,
Shubhangi

Fred 1096 posts Joined 08/04
26 Jun 2008

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.

ashija 10 posts Joined 01/07
03 Jul 2008

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

ashija 10 posts Joined 01/07
03 Jul 2008

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

jonesj2 16 posts Joined 02/09
26 Oct 2011

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' )   ,
 

You must sign in to leave a comment.