Error message

  • Warning: Invalid argument supplied for foreach() in MemCacheDrupal->wildcards() (line 264 of /appl2/devx/drupal/sites7/all/modules/memcache/memcache.inc).
  • Warning: Invalid argument supplied for foreach() in MemCacheDrupal->wildcards() (line 264 of /appl2/devx/drupal/sites7/all/modules/memcache/memcache.inc).
All Forums Tools
JBrixey 2 posts Joined 12/04
03 Sep 2009
How to Convert VARCHAR(30) to a DATE in Fastload

I have a table coming in from Sybase using BCP. It is pipe delimited. There is a Date field I will call RecordDate. It is Varchar (30) but it needs to be a DATE so that it can be compared to another table using the date and some other fields. I tried several ways to format the fastload program to bring in and format a DATE instead of a VARCHAR(30) but the program will only work if I bring it in as a varchar (30). I looked at the input file and it is pipe delimited, but in the varchar(30) field, I only see something like this: 12/31/2008 I am a supervisor and have been hands off for awhile, but we are short handed and I'd like to get this working. Any suggestions? Please? Thanks.

feinholz 771 posts Joined 05/08
03 Sep 2009

I guess I am not clear on what you are asking.When processing delimited data, FastLoad requires that the DEFINE statement be comprised of VARCHAR fields.FastLoad will read in the fields, and send them to Teradata. Teradata will perform any type of data conversions.Therefore, make sure your table has the column properly defined and formatted so that the incoming text string will be interpreted correctly and the data inserted into the table correctly.

--SteveF

JBrixey 2 posts Joined 12/04
03 Sep 2009

I'm sorry my question is not clear, but I appreciate your answer. The script defines the field as varchar(30) and when fastload loads the table, that's what works. In viewing the data, I see 12/31/2008 or something like that (10 characters). What our users need this field to be is a date, not a varchar(30), but I cannot figure out how to edit the fastload script to do that.

feinholz 771 posts Joined 05/08
03 Sep 2009

If the data is coming in in delimited format, you cannot change the fields of the DEFINE statement.They must be VARCHAR.I am not sure why that is important to the customer.The DEFINE statement describes what the data looks like to the FastLoad utility. That is all it is for. The FastLoad script cannot perform any conversions, so I am not sure why the customer insists on specifying the data as a DATE field.

--SteveF

Jimm 298 posts Joined 09/07
04 Sep 2009

Insert Into Tbl(DTFld )Values (:DTFld (Date, Format 'mm/dd/yyyy');Should work as long as the date is left aligned. If it is right aligned, you will have to specify the 20 spaces in the format command.If it may be anywhere in the input field, or the month/ day number are one digit fields when they are less than 10, use MLoad instead of Fastload. With MLoad, you can use any of the scalar SQL functions on the input data - like trim/ substring/ index. Unless you have millions or rows, performance is comparable.

27 Oct 2009

Or simpler still, once you have successfully loaded the data on to the table as a VARCHAR (ie you see the DATE 10/01/1900 etc), SELECT INSERT this data onto another staging area table with a CONVERSION from VARCHAR to DATE using appropriate syntax (for this column alone while all other columns get a direct SELECT INSERT without conversion). This will solve your problem of wanting the data in DATE format but using a FLOAD which only works with VARCHAR.

kae1 6 posts Joined 08/11
14 Oct 2011

(DATE, FORMAT 'MM/DD/YYYY') is exactly what I needed.  Thanks, Jimm!

You must sign in to leave a comment.