如何在 POSTGRESQL 中从日期中提取年份

日期是在’YYYY-MM-DD’文本格式，现在我需要提取的年份部分，必须在数字。我需要这个转换是在单一的步骤，因为我需要在其他应用程序中使用，我不能创建新的变量。

TO _ DATE (t0.AESTDTC，‘ YYYY-MM-DD’) ，‘ YYYY-MM-DD’用这个我能够转换到日期，但是现在我需要单步从这个日期提取年份？有人能帮我吗？

174279 次浏览
小开

Try

select date_part('year', your_column) from your_table;

or

select extract(year from your_column) from your_table;
小开

answer is;

select date_part('year', timestamp '2001-02-16 20:38:40') as year,
date_part('month', timestamp '2001-02-16 20:38:40') as month,
date_part('day', timestamp '2001-02-16 20:38:40') as day,
date_part('hour', timestamp '2001-02-16 20:38:40') as hour,
date_part('minute', timestamp '2001-02-16 20:38:40') as minute
小开

Choose one from, where :my_date is a string input parameter of yyyy-MM-dd format:

SELECT EXTRACT(YEAR FROM CAST(:my_date AS DATE));

or

SELECT DATE_PART('year', CAST(:my_date AS DATE));

Better use CAST than :: as there may be conflicts with input parameters.

小开

You may try to_char(now()::date, 'yyyy')
If text, you've to cast your text to date to_char('2018-01-01'::date, 'yyyy')

See the PostgreSQL Documentation Data Type Formatting Functions

小开

This line solved my same problem in postgresql:

SELECT DATE_PART('year', column_name::date) from tableName;

If you want month, then simply replacing year with month solves that as well and likewise.

小开 
SELECT TO_CHAR(CURRENT_DATE, 'YYYY')

提交答案