sqloracle-databasetoadpeoplesoft

SQL query for extracting year from a date

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.

Solution

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