From SQL assistant when we export records into flat file, It give output file date filed as YYYY-MM-DD.Now again when we try to load this file into table, it gives problem at date field. Problem is resolved if we remove - (dashes) from date.How can we remove dashes from date field while exporting records.I need date format in flat file in YYYYMMDD format
select cast(calendar_date as date format 'yyyymmdd') (char(8)) from sys_calendar.calendar where calendar_date=date
I am having the date values like "01-JAN-17"
i want cast it as "01-01-2017", i tried with below function
cast('01-JAN-17 as date format 'dd-mmm-yy')
but it is coming like "01-01-1917"
but i want like "01-01-2017" please help any one on this query.
I have two solutions for you, but first a word as to what's going on. Teradata has to make assumptions in character string dates with two-digit years: is 19xx or 20xx meant? We provide a DBS Control parameter, Century Break, that tells the DBS where the dividing line is. For backward compatibility, the default dividing line is 0 (all xx are taken as 19xx). If the dividing line is set to 100, all xx are taken as 20xx; if the dividing line is 40, 00 to 39 are taken as 20xx and 40 to 99 are taken as 19xx. (You might wonder why we didn't pick a different default; aside from the backward compatibility issue, there was no default value that we could pick that would satisfy all users.) For information is available in the Utilities manual (for TD13.10, B035-1102-109A, page 304 in chapter 12).
Anyway, the simplest (for you) solution would be to convince your DBA to change the Century Break value. Of course, that affects all applications on the system (and all systems should have the same value, to avoid nightmares).
The second solution (actually, a set of solutions) should work (for generality, I'm using :In_Date rather than a string date literal—assume that In_Date is defined as CHAR(9)):
CAST(:In_Date AS DATE FORMAT 'dd-mmm-yy') + INTERVAL '100' YEAR
WHEN SYSLIB.year_of_calendar(CAST(:In_Date AS DATE FORMAT 'dd-mmm-yy')) < 2000
THEN CAST(:In_Date AS DATE FORMAT 'dd-mmm-yy') + INTERVAL '100' YEAR
ELSE CAST(:In_Date AS DATE FORMAT 'dd-mmm-yy')
Note: I just checked the first and third approaches (both work), but couldn't check the second one at this time.
Anyway, I hope that helps.
Oops. Forgot to explain that third approach—it simply uses brute force to make the year four digits before doing the cast.