CODEWITHSUNDEEP
postgresqldatecastinginteger
Thomas_SO
Thomas_SO

Reputation: 2571

POSTGRESQL: how to convert date to integer?

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.

Upvotes: 9

Views: 39502

Answers (2)

user14461354
user14461354

Reputation: 11

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 ;

Upvotes: 1

Pavel Stehule
Pavel Stehule

Reputation: 45770

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.

Upvotes: 22

Related Questions