Comments

• Daniel_Leybovic

  October 2013

  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)
  

  0
• soma_1

  October 2013

  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!!!

  0
• soma_1

  October 2013

  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;

  0
• soma_1

  October 2013

  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 !!!

  0