need to extract century(20th or 21st) based on the date given

soma_1

I am trying to extract a date from a string. On conversion, I am getting incorrect century for the year actual value expected value 123051  = 1951-12-30 Below is the query and result on date conversion: SELECT TO_DATE('123051','mmddyy'); 2051-12-30 Any year between 70 & 99 are auto converted to 20th century select to_date('030270','MMDDYY'); to_date ------------ 1970-03-02

Daniel_Leybovic

daniel=> create table soma (dt char(6));
CREATE TABLE

daniel=> copy soma from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 110813
>> 123051
>> \.

daniel=> select * from soma;
   dt  
--------
 110813
 123051
(2 rows)

daniel=> \p
select dt,
       decode(dt % 100 < 14, 0, 19, 20) "century",
       date(decode(dt % 100 < 14, 0, 19, 20) || dt % 100 || dt // 100) "date"
  from soma;

daniel=> \g
   dt   | century |    date   
--------+---------+------------
 110813 |      20 | 2013-11-08
 123051 |      19 | 1951-12-30
(2 rows)

soma_1

wonderful... thanks for the quick response.... I have tweaked the SQL stmt as follows .. => \p select dt "rawDt", to_date(dt,'mmddyy') "toDt", decode(dt % 100 < 14, 0, 19, 20) "century", dt % 100 "year", date(decode(dt % 100 < 14, 0, 19, 20) || dt % 100 || dt // 100) "dandt", date(decode(dt % 100<(year(current_date)%100+1),0,19,20)||dt%100||substr(dt,1,4)) as dandt2, to_date(decode(dt % 100<(year(current_date)%100+1),0,19,20)||dt%100||substr(dt,1,4),'YYYYMMDD') as "newdate" from D1.dttest; Thank You!!!

soma_1

Thanks for your quick response... I have tweaked your SQL as follows... select dt "rawDt", to_date(dt,'mmddyy') "toDt", decode(dt % 100 < 14, 0, 19, 20) "century", dt % 100 "year", date(decode(dt % 100 < 14, 0, 19, 20) || dt % 100 || dt // 100) "dandt", date(decode(dt % 100<(year(current_date)%100+1),0,19,20)||dt%100||substr(dt,1,4)) as dandt2, to_date(decode(dt % 100<(year(current_date)%100+1),0,19,20)||dt%100||substr(dt,1,4),'YYYYMMDD') as "newdate" from D1.dttest;

soma_1

Thanks for your quick response.... I have tweaked your query as follows... select dt "rawDt", to_date(dt,'mmddyy') "toDt", decode(dt % 100 < 14, 0, 19, 20) "century", dt % 100 "year", date(decode(dt % 100 < 14, 0, 19, 20) || dt % 100 || dt // 100) "dandt", date(decode(dt % 100<(year(current_date)%100+1),0,19,20)||dt%100||substr(dt,1,4)) as dandt2, to_date(decode(dt % 100<(year(current_date)%100+1),0,19,20)||dt%100||substr(dt,1,4),'YYYYMMDD') as "newdate" from D1.dttest; rawDt | toDt | century | year | dandt | dandt2 | newdate --------+------------+---------+------+------------+------------+------------ 030292 | 1992-03-02 | 19 | 92 | | 1992-03-02 | 1992-03-02 081013 | 2013-08-10 | 20 | 13 | | 2013-08-10 | 2013-08-10 050847 | 2047-05-08 | 19 | 47 | | 1947-05-08 | 1947-05-08 030270 | 1970-03-02 | 19 | 70 | | 1970-03-02 | 1970-03-02 110813 | 2013-11-08 | 20 | 13 | 2013-11-08 | 2013-11-08 | 2013-11-08 040229 | 2029-04-02 | 19 | 29 | | 1929-04-02 | 1929-04-02 010756 | 2056-01-07 | 19 | 56 | | 1956-01-07 | 1956-01-07 030328 | 2028-03-03 | 19 | 28 | | 1928-03-03 | 1928-03-03 122683 | 1983-12-26 | 19 | 83 | 1983-12-26 | 1983-12-26 | 1983-12-26 040328 | 2028-04-03 | 19 | 28 | | 1928-04-03 | 1928-04-03 123051 | 2051-12-30 | 19 | 51 | 1951-12-30 | 1951-12-30 | 1951-12-30 000820 | 2020-01-08 | 19 | 20 | | | 1920-01-08 080328 | 2028-08-03 | 19 | 28 | | 1928-08-03 | 1928-08-03 (13 rows) Time: First fetch (13 rows): 56.604 ms. All rows formatted: 57.989 ms Thanks !!!