How to Extract Year and Month from Date in Postgresql Without Using To_Char() Function

How to extract year and month from date in PostgreSQL without using to_char() function?

date_part(text, timestamp)

e.g.

date_part('month', timestamp '2001-02-16 20:38:40'),
date_part('year', timestamp '2001-02-16 20:38:40') 

http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

Postgres: How do I extract year and month from a date?

I just want to point out that it is often convenient to leave the value as a date. If so, use date_trunc():

select date_trunc('month', order_date) as yyyymm

If you really want a string, you should accept Nick's answer.

How to extract months and year from a timestamp in postgreSQL table

You can format the interval using TO_CHAR():

TO_CHAR(AGE(NOW(), date_of_birth), 'YY "Year" MM "month"') AS age

However, a more colloquial solution is to build the parts separately:

   CONCAT_WS(' ',
             (CASE WHEN EXTRACT(YEAR FROM v.age) = 0 THEN NULL
                   WHEN EXTRACT(YEAR FROM v.age) = 1 THEN '1 year'
                   ELSE EXTRACT(YEAR FROM v.age) || ' years'
              END),
             (CASE WHEN EXTRACT(MONTH FROM v.age) = 1 THEN '1 month'
                   ELSE EXTRACT(MONTH FROM v.age) || ' months'
              END)
           ) as age

This handles things like "1 year" versus "2 years".

Here is a db<>fiddle.

PostgreSql - extract Month + year from date

Use to_char() for format the timestamp value:

to_char(to_timestamp(e.startts), 'mm/yy') as "Date"

postgres sql to extract year-month

You need to use TO_DATE first, to convert the column to a proper date. Then use TO_CHAR to format as you want:

SELECT TO_CHAR(TO_DATE(first_day_month, 'DD/MM/YYYY'), 'MM/YYYY') AS my
FROM yourTable;

Note that in this case since the text month year you want is actually just the right substring, you could also directly use RIGHT here:

SELECT RIGHT(first_day_month, 7)
FROM yourTable;

Finally, note that YYYY/MM would generally be a better format to use, as it sorts properly. So perhaps consider using this version:

SELECT TO_CHAR(TO_DATE(first_day_month, 'DD/MM/YYYY'), 'YYYY/MM') AS ym
FROM yourTable;

extract quarter from date field and display like Q4:YYYY format in query results

you only need to_char()

to_char(reviewdon, '"Q"q:yyyy')

---

Related Topics