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.