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