Oracle SQL selecting date-CodePudding

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 :

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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).

CodePudding user response：

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

CodePudding user response：

Oracle databases are normally configured with a default date format of DD-MON-YY, or DD-MON-YYYY.

If you want to use a different format you have tell Oracle what the format is. So:

SELECT title,lastname,birthdate,country 
FROM EMPLOYEES 
WHERE title='Sales Representative' 
AND BIRTHDATE <= TO_DATE('01/01/1950 00:00:00', 'DD/MM/YYYY HH24:MI:SS');

Using the default, no need to supply the format mask:

SELECT title,lastname,birthdate,country 
FROM EMPLOYEES 
WHERE title='Sales Representative' 
AND BIRTHDATE <= TO_DATE('01-JAN-1950');