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.
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.
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.
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.
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.
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.
(DATE, FORMAT 'MM/DD/YYYY') is exactly what I needed. Thanks, Jimm!