2

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 :

enter image description here

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Crej00
  • 19
  • 2
  • If it's before **or in** 1950, why are you filtering for the date <= 01 January 1950? Never mind how to code anything - that doesn't make sense regardless of code. Someone born on 23 May 1950 should be included, no? –  May 26 '22 at 17:51

4 Answers4

0

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
0

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

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
  • It should be pointed out this (extracting the year from a date) will be much faster than casting a literal to a date. – Hogan May 26 '22 at 17:01
  • 2
    @Hogan - what are you talking about? The only "literal" here is 1 Jan. 1950 (which is wrong anyway), and casting that literal to a date is done by the compiler, it doesn't even reach execution as a string literal. It would be bad if string **data** had to be converted, but we assume `birthdate` is of `date` data type, as it should be. To the contrary, extracting year from a date, instead of comparing date directly to a **date** literal, will prevent the use of an index on `birthdate` (if there is one), and in any case will be inefficient because it calls a funtion on every row. –  May 26 '22 at 17:54
  • @mathguy hmmm... fair point. We need someone to do some testing to see which is faster -- now I'm curious. (Now that I think about it they might be the same speed since the optimizer is good it probably generates the same code for both). – Hogan May 26 '22 at 18:21
  • @Hogan `extract` is a function that returns number, so after it was applied the optimizer works with number, not date. Plain date (literal or `to_date`) would be generally faster because of the above mentioned compile-time calculation and usage of index/partitioning. In case of full table scan they will be the same. – astentx May 26 '22 at 18:28
0

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
TechieWords
  • 92
  • 6
0

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55