Thread: Q: How to convert int to date?
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 */
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? =-
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/ ----------------------------------
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/ ----------------------------------
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 */