SQL query for extracting year from a date

C

5

20

I am trying to create a query that gets only the year from selected dates. I.e. select ASOFDATE from PSASOFDATE; returns 11/15/2012, but I want only 2012. How can I get only the year? I know the YEAR function can be used, but I'm not sure how.

Crinkle answered 19/11, 2012 at 14:6 Comment(3)

The question was retagged from mysql to oracle. This introduced quite a bit of confusion in this Q&A. Knowing this, the answers are a lot less surprising. – Sarnen 15/10, 2016 at 14:59

docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm – Flaccid 17/12, 2018 at 9:10

@Sarnen If I recall right... at that time, there was also a confirmation OP being a puppet account... – Ozoniferous 26/6, 2020 at 14:54

O

-2

Edit: due to post-tag 'oracle', the first two queries become irrelevant, leaving 3rd query for oracle.

For MySQL:

SELECT YEAR(ASOFDATE) FROM PASOFDATE

Editted: In anycase if your date is a String, let's convert it into a proper date format. And select the year out of it.

SELECT YEAR(STR_TO_DATE(ASOFDATE, '%d-%b-%Y')) FROM PSASOFDATE

Since you are trying Toad, can you check the following code:

For Oracle:

SELECT EXTRACT (TO_DATE(YEAR, 'MM/DD/YY') FROM ASOFDATE) FROM PSASOFDATE;

Reference:

Ozoniferous answered 19/11, 2012 at 14:7 Comment(10)

For you to get a better understanding on date, time related manupulations, please take a look at this link as well. Hope that helps in the long run :) – Ozoniferous 19/11, 2012 at 14:10

That's because i misspelled your table name I guess... can you try the DATE(date column) and your proper column name and table name :) – Ozoniferous 19/11, 2012 at 14:12

what would the query look like exactly? – Crinkle 19/11, 2012 at 14:14

Sorry, I got disconnected. It seems you have voted for John Woo's answer. So did it work for you? Frankly, it's important to dig into the issue you are getting. I am more curious to know if you are still getting the error if not which answer solved that issue and got you the correct results. – Ozoniferous 19/11, 2012 at 14:30

I didn't vote for anyones. Im pretty sure all of your answers are correct, but I don't know why it won't work. I'm using Peoplesoft and Toad as the SQL interface... – Crinkle 19/11, 2012 at 14:35

I was referring to votes, coz I had the impression his answers solved your issue. Anyway, you may want to use CAST and EXTRACT if it's TOAD. CAST, EXTRACT – Ozoniferous 19/11, 2012 at 14:44

What would the query look like using CAST? – Crinkle 19/11, 2012 at 15:5

Did you try the above extract code? I have updated to convert the string to Date and then use EXTRACT to get the year out. And if it works please let us know. – Ozoniferous 19/11, 2012 at 15:7

I am glad. So you do not actually need CAST to be used. However I have updated the answer to convert your date into DATE. That also says you are using TOAD SQL which is more of the Oracle PL/SQL decendent type. Not MYSQL :) The invalid identifier was due to the fact we used wrong language keywords/syntaxes in your TOAD query. – Ozoniferous 19/11, 2012 at 15:11

The first two queries are for MySQL, the third one is for Oracle. Since the question is tagged oracle, this is fairly misleading and should be explicitly stated in the question. – Sarnen 15/10, 2016 at 14:36

F

53

This worked for me:

SELECT EXTRACT(YEAR FROM ASOFDATE) FROM PSASOFDATE;

Fernferna answered 26/1, 2014 at 4:50 Comment(0)

B

28

How about this one?

SELECT TO_CHAR(ASOFDATE, 'YYYY') FROM PSASOFDATE

Baccate answered 21/11, 2012 at 15:42 Comment(2)

it return a string not a number, bad idea – Hypersensitize 16/1, 2017 at 15:33

@Baccate I agree with you Juan. Good answer. Totally OK to use a string when you consistently use a 4 digit year. If you add the day and/or month and you want to SORT, sort by the date column itself or by a to_char string with the 'YYYY/MM/DD' or similar format to get the correct order. – Aerology 9/1, 2020 at 17:13

S

5

just pass the columnName as parameter of YEAR

SELECT YEAR(ASOFDATE) from PSASOFDATE;

another is to use DATE_FORMAT

SELECT DATE_FORMAT(ASOFDATE, '%Y') from PSASOFDATE;

SQLFiddle Demo

UPDATE 1

I bet the value is varchar with the format MM/dd/YYYY, it that's the case,

SELECT YEAR(STR_TO_DATE('11/15/2012', '%m/%d/%Y'));

SQLFiddle Demo

LAST RESORT if all the queries fail

use SUBSTRING

SELECT SUBSTRING('11/15/2012', 7, 4)

SQLFiddle Demo

Significant answered 19/11, 2012 at 14:7 Comment(6)

how about the second one using DATE_FORMAT? – Significant 19/11, 2012 at 14:8

what is the data type of ASOFDATE? and how is the value formatted? – Significant 19/11, 2012 at 14:8

it displayed the same error for the second query. I am not sure what the data type is..let me try to pull it up.. – Crinkle 19/11, 2012 at 14:10

@StackOver I updated the answer, see the result of the fiddle. – Significant 19/11, 2012 at 14:11

@StackOver see my updated answer, the format of your date is M/d/Y right? try converting it back to date. – Significant 19/11, 2012 at 14:17

This answer is MySQL only. After the retag to oracle, this one is completely invalid. I suggest adding a warning to the top of this answer. – Sarnen 15/10, 2016 at 15:3

I

4

SELECT date_column_name FROM table_name WHERE EXTRACT(YEAR FROM date_column_name) = 2020

Insolvency answered 21/7, 2020 at 17:25 Comment(0)

O

-2

Edit: due to post-tag 'oracle', the first two queries become irrelevant, leaving 3rd query for oracle.

For MySQL:

SELECT YEAR(ASOFDATE) FROM PASOFDATE

Editted: In anycase if your date is a String, let's convert it into a proper date format. And select the year out of it.

SELECT YEAR(STR_TO_DATE(ASOFDATE, '%d-%b-%Y')) FROM PSASOFDATE

Since you are trying Toad, can you check the following code:

For Oracle:

SELECT EXTRACT (TO_DATE(YEAR, 'MM/DD/YY') FROM ASOFDATE) FROM PSASOFDATE;