Thread

1. Q: How to convert int to date?

   Mattias Kregert <matti@algonet.se> — 1998-06-23T10:37:57Z

   How do I convert INTEGER to DATE in 6.3.2?
   
   I have a table 'mytbl' with a column 'd' which is
   an integer, containing dates in YYMMDD form. Now,
   how do I convert these integer dates to date dates?
   
   I tried...
     ALTER TABLE mytbl ADD COLUMN dd date;
     UPDATE mytbl SET dd=date(d+1900);
   ...but there is no int-to-date function!
   
     UPDATE mytbl SET dd=((d+1900)::unknown)::date;
   ...does not work either. No int-to-unknown function!
   
   So, how do I cast to type unknown?
   
   
   I have ... let's see... around 500-600 days
   to solve this problem ;)
   
   /* m */
   
   

2. Re: [GENERAL] Q: How to convert int to date?

   Marin D <marin@cybernet.bg> — 1998-06-23T11:03:16Z

   
   On Tue, 23 Jun 1998, Mattias Kregert wrote:
   
   ->How do I convert INTEGER to DATE in 6.3.2?
   ->
   ->I have a table 'mytbl' with a column 'd' which is
   ->an integer, containing dates in YYMMDD form. Now,
   
   
   This is quite bad representation of date. U know Y2K, so on...
   
   
   ->how do I convert these integer dates to date dates?
   ->
   
   
   I would propose to modify your table with DATE column, dump your old 
   table, write a script modifying the 980622 to 98-06-22 and import it back
   in the new table
   
   or write your own function dealing with such 'dates'
   
   Best regards
   
   	Marin 
   
   
             -= Why do we need gates in a world without fences? =-
   
   
   
   

3. Re: [GENERAL] Q: How to convert int to date?

   Patrice Hédé <patrice@idf.net> — 1998-06-23T16:31:00Z

   On Tue, 23 Jun 1998, Mattias Kregert wrote:
   
   > How do I convert INTEGER to DATE in 6.3.2?
   > 
   > I have a table 'mytbl' with a column 'd' which is
   > an integer, containing dates in YYMMDD form. Now,
   > how do I convert these integer dates to date dates?
   
   I tried to do :
   
   test=> SELECT 980623::text::date;
     ?column?
   ----------
   23-06-0098
   (1 row)
   
   Maybe you should try something along this way :)
   
   Patrice
   
   --
   Patrice HÉDÉ --------------------------------- patrice@idf.net -----
   Nous sommes au monde. [...] La croyance en un esprit absolu ou en un
   monde en soi détaché de nous n'est qu'une rationalisation  de  cette
   foi primordiale.  --- Merleau-Ponty, Phénoménologie de la Perception
   ----- http://www.idf.net/patrice/ ----------------------------------
   
   
   

4. Re: [GENERAL] Q: How to convert int to date?

   Patrice Hédé <patrice@idf.net> — 1998-06-23T16:41:25Z

   On Tue, 23 Jun 1998, Mattias Kregert wrote:
   
   > How do I convert INTEGER to DATE in 6.3.2?
   > 
   > I have a table 'mytbl' with a column 'd' which is
   > an integer, containing dates in YYMMDD form. Now,
   > how do I convert these integer dates to date dates?
   > 
   > I tried...
   >   ALTER TABLE mytbl ADD COLUMN dd date;
   >   UPDATE mytbl SET dd=date(d+1900);
   > ...but there is no int-to-date function!
   
   Forget my first reply, there is no date(text) function either...
   
   But you can do that :
   
   test=> select test, (test+19000000)::text::datetime::date from testint;
     test|      date
   ------+----------
   980101|01-01-1998
   971121|21-11-1997
   720306|06-03-1972
   (3 rows)
   
   The test field is a int4 field, so you can do that (not very nice, but it
   works) :)
   
   Hope this helps !
   
   Patrice
   
   --
   Patrice HÉDÉ --------------------------------- patrice@idf.net -----
   Nous sommes au monde. [...] La croyance en un esprit absolu ou en un
   monde en soi détaché de nous n'est qu'une rationalisation  de  cette
   foi primordiale.  --- Merleau-Ponty, Phénoménologie de la Perception
   ----- http://www.idf.net/patrice/ ----------------------------------
   
   
   

5. Re: [GENERAL] Q: How to convert int to date?

   Mattias Kregert <matti@algonet.se> — 1998-06-24T07:49:37Z

   Patrice Hédé wrote:
   >
   > Forget my first reply, there is no date(text) function either...
   
   Yes, I know. I thought pg would automagically cast to 'unknown'
   when it could not find a valid conversion, and then try again.
   Perhaps that could solve many problems.
   
   
   >
   > But you can do that :
   > 
   > test=> select test, (test+19000000)::text::datetime::date from testint;
   >   test|      date
   > ------+----------
   > 980101|01-01-1998
   > 971121|21-11-1997
   > 720306|06-03-1972
   > (3 rows)
   > 
   > The test field is a int4 field, so you can do that (not very nice, but it
   > works) :)
   > 
   > Hope this helps !
   
   Yes! It does. This is exactly what I want! Thank you!
   
   /* m */