Q: How to convert int to date?

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 */