Doubt with month and year extraction in Postgresql date

Asked

Viewed 3,710 times

2

I am using a select command where I take the month and year of the date as follows:

extract(year from D.dt_ficha) + extract(month from D.dt_ficha)

Only instead of showing up like this: 201711 November 2017, it is adding 2017 + 11 2028.

How to correct?

sql postgresql select

  • try || instead of +

  • ERROR: operator does not exist: double Precision || double Precision

  • cast(Extract(year from D.dt_fiche) as text) || cast(Extract(Month from D.dt_fiche) as text) did not test because I do not have postgresql

  • @Motta tested it like this, but when the month is less than 10, it doesn’t show zero. but I’ve already solved, posted the answer below. Hug

4 answers

3

Try it like this:

SELECT  extract(year from D.dt_ficha) || '' || extract(month from D.dt_ficha)
    from D

Where D would be your table;
For me it worked properly.

1


I resolved so:

to_char(D.dt_ficha, 'YYYYMM') as data,

0

You can also use the date_trunc function:

date_trunc('month', TIMESTAMP '2001-02-16 20:38:40')

which it will obtain as a result: 

2001-02-01 00:00:00

-1

Try to use "||"

extract(year from D.dt_ficha) || extract(month from D.dt_ficha)

Browser other questions tagged sql postgresql select

You are not signed in. Login or sign up in order to post.