im trying to select title, last name, birth date and country where the title is "Sales Representative" and they are born before or in 1950.
It is giving me a error when a put the date in the code.
I thought this was correct:
SELECT title,lastname,birthdate,country FROM EMPLOYEES
WHERE title='Sales Representative' AND BIRTHDATE <= '01/01/1950 00:00:00';
The error it gives ORA-01843: not a valid month
If you can help me, thank you.
Here is the sample data of the table :
Use either ANSI date literals
and birthdate <= date '1950-01-01'
or use an explicit to_date which includes the format mask
and birthdate <= to_date( '01/01/1950 00:00:00'
,'MM/DD/YYYY HH24:MI:SS' )
Otherwise, Oracle will attempt to do the implicit conversion from a string to a date using whatever your session's nls_date_format is set to. This generally creates issues because different people, different applications, etc. all may set their NLS settings up differently. So you can very easily write code that works for you but fails when your teammate connects to the same database or code that works for you in one tool but not another because of differences in how their environments are configured.
You can just use EXTRACT and check if the year is <= 1950:
...WHERE...AND EXTRACT (YEAR FROM birthdate) <= 1950...
Take < 1950 instead of <= 1950 if 1950 should not be included (your description tells it should, your own query wouldn't).
You can try this just inputting your desired year itself,
SELECT title,lastname,birthdate,country FROM EMPLOYEES
WHERE title='Sales Representative' AND EXTRACT(YEAR FROM
TO_DATE(BIRTHDATE, 'DD-MON-YYYY')) <= 1950
ORDER BY BIRTHDATE
You can use a date literal with birthdate strictly less than that such as
...
WHERE title='Sales Representative'
AND birthdate < date'1951-01-01'
makes the query returns the desired values inclusively for the year 1950 while you might benefit the index if there's on the birthdate column since no casting, conversion or function applied to it.