Oracle SQL - Date greater than statement

Use proper date format when comparing

When dealing with Oracle dates, confirm if the dates are stored in the right DATE data type. Using VARCHAR can lead to hiccups due to implicit conversions and format inconsistencies:

--Just like you wouldn't put a square peg in a round hole, store your dates as DATE type.
SELECT * 
FROM table_name
WHERE date_column > TO_DATE('2022-06-01', 'YYYY-MM-DD');


Match the model format in TO_DATE function ('YYYY-MM-DD') with the format of your input string.

Slay the dragon: Error ORA-01861

The ORA-01861 error is one headache that occurs when there's a mismatch in formatting. Your date format in the query and the one in your database must be in harmony, like peanut butter and jelly. Here's where the NLS_DATE_FORMAT parameter becomes your knight in shining armor:

--Remember, nobody likes a mismatch. Not even your database!
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YY';


Mind your Localization

The NLS_DATE_LANGUAGE affects how date literals are interpreted by Oracle. Here's an example for American English month names:

--And just like that, Oracle speaks American 🇺🇸
ALTER SESSION SET NLS_DATE_LANGUAGE = 'American';


Set the NLS_DATE_LANGUAGE parameter to the appropriate language to ensure locale compatibility.

Using ADD_MONTHS function

Filter dates relative to current dates with the ADD_MONTHS function. For instance, list orders 6 months older than the current date:

--Rollback to 6 months, because who doesn't love revisiting the past?
SELECT * 
FROM orders
WHERE order_date <= ADD_MONTHS(sysdate, -6);


Use date literals to avoid formatting issues

You can dodge the quagmire of date formatting by using date literals DATE 'YYYY-MM-DD':

--Be like a boss!
SELECT * 
FROM table_name
WHERE date_column > DATE '2022-06-01';


Date literals remain consistent even when the NLS_DATE_FORMAT settings varies.

Indexes, not the dusty books kind

Speed up the train (or query). If there's a column you frequently query, put an index on it:

--Who likes sluggish search results anyway? Not me!
CREATE INDEX idx_date_column ON table_name(date_column);


Avoid functions directly on indexed columns, as it could result in a full table scan.

Playing well with time parts

If you want to compare just the date part of a column that also has time, you can drop the time portion with TRUNC:

--Snip-snip! Say goodbye to time portion 👋
SELECT * 
FROM table_name 
WHERE TRUNC(date_column) > condition_date;
