Cast string in format 'YY-MM-DD' as Date

General
Visitor

Cast string in format 'YY-MM-DD' as Date

Hi,

 

I am facing an issue with casting a varchar string to date. Input format of the string is 'YY-MM-DD'. When I cast it as DATE, it returns wrong result. For example:

 

SQL:

sel cast('17-02-08' as date format 'YYYY-MM-DD')

The result is: '1917-02-08' instead of '2017-02-08'.

 

Although I have fixed the issue by appending '20' to the string and then cast as below (it gives the desired result):

sel cast('20'||'17-02-08' as date format 'YYYY-MM-DD')

My question is that is there any better and scalable solution to this problem like what if date is '3017-02-08' after a century :)

1 REPLY
Teradata Employee

Re: Cast string in format 'YY-MM-DD' as Date

There is a DBS Control field called "CenturyBreak". Default is 0, which means that any 2-digit year above 0 it assumes to start with 19. You can change that field to, for example, 40. Then the date "41-12-15" as "YYYY-MM-DD" would be translated as 1941, but "39-12-15" would be translated to 2039.