Date conversion

Tools & Utilities
Enthusiast

Date conversion

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
4 REPLIES
Teradata Employee

Re: Date conversion

select cast(calendar_date as date format 'yyyymmdd') (char(8)) from sys_calendar.calendar where calendar_date=date

Re: Date conversion

Hi,

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.

bwb
Teradata Employee

Re: Date conversion

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)):

  • Assuming you know all of the years will be 19xx and you want them all to be 20xx, do the CAST but then bump the year by 100:


CAST(:In_Date AS DATE FORMAT 'dd-mmm-yy') + INTERVAL '100' YEAR

  • If you think Century Break has been changed to something other than 0 (that is, some of the years will already be 20xx, then use this:


CASE


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


END

  • CAST(SUBSTRING(:In_Date FROM 1 FOR 7) || '20' || SUBSTRING(:In_Date FROM 8) AS DATE FORMAT 'dd-mmm-yyyy')

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.

bwb
Teradata Employee

Re: Date conversion

Oops. Forgot to explain that third approach—it simply uses brute force to make the year four digits before doing the cast.