POSTGRESQL: how to convert date to integer?

Question

Within a plpgsql-function I have a simple insert statememt:

insert into table_Y (column1       ,
                     column2       ,
                     //more columns
                     int_date      , -- example: '20190714'
                     //more columns
                    )
select value1              ,
       value2              ,
       //more values
       date_value::integer ,
       //more values   
  from table_X

Column "date_value" in table table_X is of type date. Column int_date in table table_Y is of type integer ... so with the following expression:

date_value::integer

... I want to convert the value from column "date_value" to an integer (e.g. from 14. July 2019 to '20190714').

However, I'm getting the following error message:

ERROR:  cannot cast type date to integer

How can I convert the date to an integer instead?

P.S.: the solution to this question: convert date to integer in postgresql does not help in my case becasue I don't want the number of days.

Pavel Stehule · Accepted Answer · 2019-07-18 03:59:39Z

23

The direct conversion is not allowed, because usually has not sense - but you can use auxiliary conversion to text, and it should to work:

postgres=# SELECT to_char(CURRENT_DATE, 'YYYYMMDD')::integer;
┌──────────┐
│ to_char  │
╞══════════╡
│ 20190718 │
└──────────┘
(1 row)

But I have to say, so working with this representation of date is strange and unhappy.

answered Jul 18, 2019 at 3:59

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fcdt · Accepted Answer · 2020-10-16 09:23:10Z

1

Try using int rather than integer, as following query worked for me:

SELECT * date_part('year/month/day', age(CURRENT_DATE,your date))::int
       as age
       from xyz_table
       where date_part('year/month/day', age(CURRENT_DATE,your date))::int > comparsion value ;

edited Oct 16, 2020 at 9:23

fcdt

2,5215 gold badges17 silver badges27 bronze badges

answered Oct 16, 2020 at 8:49

user14461354

111 bronze badge

Collectives™ on Stack Overflow

POSTGRESQL: how to convert date to integer?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related