CODEWITHSUNDEEP
sqloracle-databasedate
gibsonsg
gibsonsg

Reputation: 35

How do I select a date in PLSQL?

I have a column with DATA_TYPE = DATE. When I query the table, an example of data from that column is 

28-APR-08

So I'm trying to query it like so:

select * from mytable where mydatecolumn = '28-APR-08'

This returns nothing. I also tried this:

select * from mytable where mydatecolumn = TO_DATE('2008/04/28', 'yyyy/mm/dd')

This also returns nothing. What am I doing wrong? Thanks!

Upvotes: 0

Views: 387

Answers (3)

Popeye
Popeye

Reputation: 35900

Barbaros has given the perfect solution but I think we can add much more description.

Whenever two digit years are used, following is the behaviour of rr and yy:

  • rr:

50-99 are interpreted as 1950-1999, i.e. previous century dates, and dates ending in 00-49 are interpreted as 2000-2049, i.e. current century dates.

  • yy:

In case of yy, there is no such descrimination and it stores 2000 to 2099 for 00-99 i.e. only current century dates.

Few examples:

To_date('20-07-89', 'dd-mm-yy') --> 20 july, 2089
To_date('20-07-89', 'dd-mm-rr') --> 20 july, 1989

To_date('20-07-19', 'dd-mm-yy') --> 20 july, 2019
To_date('20-07-19', 'dd-mm-rr') --> 20 july, 2019

Cheers!!

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18630

You see the value '28-APR-08' because of your regional settings (google NLS_DATE_FORMAT for more info). By default, only the date is shown, not the time portion. Check the following example:

CREATE TABLE just_a_date(mydate DATE);
Table JUST_A_DATE created.
INSERT INTO just_a_date(mydate) VALUES (SYSDATE);
1 row inserted.
SELECT * FROM just_a_date;
27-SEP-2019
SELECT * FROM just_a_date WHERE mydate = '27-SEP-19';
no rows selected

That is what you are seeing. The Oracle DATE datatype stores both DATE and TIME up to the second. Lets take a closer look at the data in the table just_a_date by using a format mask.

SELECT TO_CHAR(mydate, 'DD-MON-YYYY HH24:MI:SS')  FROM just_a_date;
27-SEP-2019 14:50:51

There you go. There is a time portion to it. If you query for mydate = '27-SEP-19' then Oracle will implicitely convert that to 27-SEP-2019 00:00:00 and you will get no rows. You will get the rows if you specify the time portion up to the second.

SELECT * FROM just_a_date WHERE mydate = TO_DATE('27-SEP-2019 14:50:51','DD-MON-YYYY HH24:MI:SS');
27-SEP-2019

Or ... if you don't care about the time portion, you can use the TRUNC function. That will remove the time portion from the DATE column.

SELECT * FROM just_a_date WHERE TRUNC(mydate)  = TO_DATE('27-SEP-2019','DD-MON-YYYY');
27-SEP-2019

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65158

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

So,use 'DD-MON-RR' as date format for your case :

select * from mytable where mydatecolumn = to_date('28-APR-08','DD-MON-RR')

Upvotes: 0

Related Questions