CODEWITHSUNDEEP
amazon-redshift
Atul
Atul

Reputation: 55

Extract Year from a character variable in redshift query editor

I have a variable in my redshift table. Variable name is service_date and its a character variable. i want to get the year from this variable. i am using below code but it's not working. 

extract(year from (cast(service_date as date))

Sample data of date-

17-Mar-10
2-Mar-10
19-Nov-10
19-Nov-10 etc.

Upvotes: 1

Views: 851

Answers (1)

Red Boy
Red Boy

Reputation: 5729

You could do it following way--

From Timestamp

 select DATE_PART_YEAR('2019-01-15 04:47:22');
 date_part_year
 -----------
       2019
(1 row)

From Date

 select  DATE_PART_YEAR('2019-01-15');

 date_part_year
 -----------
       2019
 (1 row)

But looking into your sample data, it could be understood easily that your data is in text format not in date format, hence it needs to be casted first to the date, then the DATE_PART_FUNCTION could be used.

select DATE_PART_YEAR(to_date('10-Nov-15','DD-Mon-YY'));

Here to_date function convert text to date and then extracts the Year. Hope this answers your question.

Upvotes: 1

Related Questions