dd-mm-yyyy to yyyy-mm-dd SQL

Question

I am working on SQL assignment in my school. There is a problem with DATE for me. There is a query given.

INSERT INTO myTable (t_id, Birthdate) VALUES (1, TO_DATE('01-Jun-2005','dd-mm-yyyy'));
INSERT INTO myTable (t_id, Birthdate) VALUES (2, TO_DATE('04-Apr-2002','dd-mm-yyyy'));
INSERT INTO myTable (t_id, Birthdate) VALUES (3, TO_DATE('21-Mar-2004','dd-mm-yyyy'));
INSERT INTO myTable (t_id, Birthdate) VALUES (4, TO_DATE('05-Dec-2002','dd-mm-yyyy'));
INSERT INTO myTable (t_id, Birthdate) VALUES (5, TO_DATE('13-Sep-2003','dd-mm-yyyy'));

I want to get t_id where YEAR is 2002. I tried following code,

SELECT t_id FROM myTable WHERE YEAR(Birthdate) = '2002';

But it gives me syntax error. I searched it on internet. it says that YEAR works only with 'yyyy-mm-dd' format. But, on my assignment question has 'dd-mm-yyyy' format. How can I answer my question? Please help me to get out from this hell.

if you tag your RDMS product we don't have guess anymore.. SQL is only about a teh standard language specification on this website.. — Raymond Nijland, Oct 29 '19 at 14:38
But `TO_DATE(..)` suggests your are using Oracle database or PostgreSQL as RDMS? — Raymond Nijland, Oct 29 '19 at 14:39

score 4 · Answer 1 · edited Oct 29 '19 at 15:13

year() is not supported in all RDBMS (notably, Oracle, or Postgres, that you seem to be using, do not support it). Use sql standard function extract() instead:

extract(year from Birthdate)

This works if Birthdate is of date-like datatype. If it's a string in some format, then you need to convert it to a date first, using to_date() in Oracle, like:

extract(year from to_date(Birthdate, 'yyyy-mm-dd'))

Other RDBMS have different functions to convert strings to dates.

Finally please note that it is more efficient to avoid using functions when filtering, since this defeats an index on the column being filtered. You usually want to do comparaisons on the whole date, so:

Birthdate >= to_date('2002-01-01', 'yyyy-mm-dd') and
Birthdate < to_date('2003-01-01', 'yyyy-mm-dd')

Note: the format descriptor for '01-Jun-2005' would be 'dd-mon-yyyy', not 'dd-mm-yyyy'.

score 2 · Answer 2 · edited Oct 29 '19 at 15:04

2

You could use:


SELECT t_id FROM myTable WHERE DATEPART(YEAR, Birthdate) = '2002'

edited Oct 29 '19 at 15:04

KC Junior

49
6

answered Oct 29 '19 at 14:26

B. Seberle

370
1
8

1

For a bit broader explanation to this answer see ["6 Functions to Get the Day, Month and Year from a Date in SQL Server"](https://database.guide/6-functions-to-get-the-day-month-and-year-from-a-date-in-sql-server/) . This explains why DATEPART is an option. – sophievda Oct 29 '19 at 14:35
1

This seams totally related to SQL Server (MSSQL) which the topicstarter is not even using as `TO_DATE` is a non valid MSSQL function, it suggests the topicstarter is using Oracle DB or PostgreSQL.. Also the SQL tag does not mean a MSSQL here it is just related to the SQL standard specification – Raymond Nijland Oct 29 '19 at 14:41
2

`DATEPART` is in this case also not a option. @sophievda TO_DATE suggest the topicstarter is using Oracle DB (best fit `extract(..)` or PostgreSQL (`DATE_PART()` and should also support `extract(..)`) .. See mine above comment.. – Raymond Nijland Oct 29 '19 at 14:48
Great explanation and thanks for the feedback @RaymondNijland, makes it even more clear. I meant to give a source and a bit broader explanation through that to this answer as the 'why' is missing for this is an answer (as in being a suggestion for edit; not as in being the answer to the topic). I should have been more clear with ending my last sentence with: 'This explains why according to this answer DATEPART is an option.' – sophievda Oct 31 '19 at 10:41

score 1 · Answer 3 · answered Oct 29 '19 at 15:23

1

:)

This works for me:

SELECT t_id FROM your_table WHERE DATE_FORMAT(Birthdate, '%Y') = 2002

answered Oct 29 '19 at 15:23

Cecilia Pérez

31
3

ur code shows me just only last two digits of the year, Why is that? – KC Junior Oct 29 '19 at 15:29
I change order to see how your code is running ```select DATE_FORMAT(Birthdate, '%y'), t_id From myTable;``` – KC Junior Oct 29 '19 at 15:49
1

It's because you're using %y instead of %Y. %y returns the last two digits and %Y returns full year. – Cecilia Pérez Oct 29 '19 at 15:52

dd-mm-yyyy to yyyy-mm-dd SQL

3 Answers3