How to query date on Oracle database dd/mm/yy-CodePudding

I'm having some trouble to filter on dates on a Oracle database.

I have a simple table :

----------------------
| MyTable            |
----------------------
| id INT             |
| name VARCHAR(255)  |
| date_creation DATE |
----------------------

Here some example data :

---------------------------------
| id | name     | date_creation |
---------------------------------
| 1  | SomeText | 03/11/16      |
| 2  | Sample   | 04/11/16      |
| 3  | Sample1  | 13/07/22      |
| 4  | Sample2  | 11/07/22      |
---------------------------------

I want to extract every row after 01/07/2022

SELECT 
    id, name, date_creation
FROM
    MyTable            
WHERE 
    date_creation > to_date("01/07/22")

Error :

ORA-00904: "01/07/22" : identificateur non valide
00904. 00000 -  "%s: invalid identifier"

Also tried

SELECT 
    id, name, date_creation
FROM
    MyTable            
WHERE 
    date_creation > DATE "01/07/22"

Error :

ORA-00936: expression absente
00936. 00000 -  "missing expression"

And

SELECT 
    id, name, date_creation
FROM
    MyTable            
WHERE 
    date_creation > "01/07/22"

Error :

ORA-00904: "01/07/22" : identificateur non valide
00904. 00000 -  "%s: invalid identifier"

CodePudding user response：

01/07/22 is a string. Therefore you need to enclose it in single-quotes, not double-quotes (the latter are used for case-sensitive identifiers, e.g. "ColumnName").

To convert a string into a date, you can either use the date literal (note that this expects the string to be in YYYY-MM-DD format):

DATE '2022-07-01'

or to_date() along with a format mask, describing the format of the string:

to_date('01/07/2022', 'dd/mm/yyyy')

Note also that I've put the year in 4-digit format; if you know the full year, there is no reason for you to use the shortened format and expect Oracle to guess correctly which century you're talking about. Y2K may have been over 22 years ago, but that's no excuse to forget the lessons we learnt from it!

I personally prefer to_date() over the date literal, as it's far more flexible - you can include times in your date strings (e.g. to_date('01/07/2022 23:49:00', 'dd/mm/yyyy hh24:mi:ss')), which you can't do with the date literal.

CodePudding user response：

WITH CTE(ID,NAME,DATE_CREATION)AS
(
   SELECT 1,    'SomeText', TO_DATE('03/11/16','DD/MM/YY') FROM DUAL UNION ALL      
  SELECT 2 ,   'Sample',    TO_DATE('04/11/16','DD/MM/YY')  FROM DUAL UNION ALL     
  SELECT 3 ,   'Sample1',   TO_DATE('13/07/22','DD/MM/YY')  FROM DUAL UNION ALL      
  SELECT 4 ,   'Sample2',   TO_DATE('11/07/22','DD/MM/YY')  FROM DUAL  
)
SELECT C.ID,C.NAME,C.DATE_CREATION
FROM CTE C
WHERE C.DATE_CREATION>TO_DATE('01/07/22','DD/MM/YY')

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=041abe9145fc3015816c0b7c91623006