SQL query for extracting year from a date

Question

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.

The question was retagged from [tag:mysql] to [tag:oracle]. This introduced quite a bit of confusion in this Q&A. Knowing this, the answers are a lot less surprising. — Palec, Oct 15 '16 at 14:59
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm — prashanth, Dec 17 '18 at 09:10
@Palec If I recall right... at that time, there was also a confirmation OP being a puppet account... — bonCodigo, Jun 26 '20 at 14:54

score 46 · Answer 1 · edited Oct 15 '16 at 14:50

46

This worked for me:

SELECT EXTRACT(YEAR FROM ASOFDATE) FROM PSASOFDATE;

edited Oct 15 '16 at 14:50

Palec

12,743
8
69
138

answered Jan 26 '14 at 04:50

Gee

463
4
3

score 26 · Answer 2 · edited Oct 15 '16 at 14:53

26

How about this one?

SELECT TO_CHAR(ASOFDATE, 'YYYY') FROM PSASOFDATE

edited Oct 15 '16 at 14:53

Palec

12,743
8
69
138

answered Nov 21 '12 at 15:42

Juan

261
2
2

1

it return a string not a number, bad idea – Alberto Acuña Jan 16 '17 at 15:33
@Juan 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. – Mark Jan 09 '20 at 17:13

John Woo · Answer 3 · 2012-11-19T14:29:55.600

4

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

edited Nov 19 '12 at 14:29

answered Nov 19 '12 at 14:07

John Woo

258,903
69
498
492

how about the second one using `DATE_FORMAT`? – John Woo Nov 19 '12 at 14:08
1

what is the data type of `ASOFDATE`? and how is the value formatted? – John Woo Nov 19 '12 at 14:08
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.. – John Doe Nov 19 '12 at 14:10
@StackOver I updated the answer, see the result of the fiddle. – John Woo Nov 19 '12 at 14:11
@StackOver see my updated answer, the format of your date is `M/d/Y` right? try converting it back to date. – John Woo Nov 19 '12 at 14:17
1

This answer is MySQL only. After the retag to [tag:oracle], this one is completely invalid. I suggest adding a warning to the top of this answer. – Palec Oct 15 '16 at 15:03

Siddharth Sonwane · Answer 4 · 2020-07-23T03:01:55.543

3

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

edited Jul 23 '20 at 03:01

answered Jul 21 '20 at 17:25

Siddharth Sonwane

41
1
5

bonCodigo · Accepted Answer · 2020-06-26T14:50:51.940

-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:

edited Jun 26 '20 at 14:50

answered Nov 19 '12 at 14:07

bonCodigo

14,268
1
48
91

For you to get a better understanding on [date, time related manupulations](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html), please take a look at this link as well. Hope that helps in the long run :) – bonCodigo Nov 19 '12 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 :) – bonCodigo Nov 19 '12 at 14:12
what would the query look like exactly? – John Doe Nov 19 '12 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. – bonCodigo Nov 19 '12 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... – John Doe Nov 19 '12 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](http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/CAEF/Default.aspx) – bonCodigo Nov 19 '12 at 14:44
What would the query look like using CAST? – John Doe Nov 19 '12 at 15:05
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. – bonCodigo Nov 19 '12 at 15:07
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. – bonCodigo Nov 19 '12 at 15:11
6

The first two queries are for MySQL, the third one is for Oracle. Since the question is tagged [tag:oracle], this is fairly misleading and should be explicitly stated in the question. – Palec Oct 15 '16 at 14:36

SQL query for extracting year from a date

5 Answers5