Retrieving Weekend Data Rows Only in Oracle

Question

Hi,

How to query rows in a table where the created_date column value in the table falls on a weekend date ONLY, i.e. Saturday/Sunday using Oracle SQL..

Thanks

Answer 1

WHERE to_char(created_date, 'D') >= 6

To improve the performance you could create function based index to_char(created_date, 'D')

Answer 2

SELECT *
    FROM YourTable
    WHERE  TO_CHAR (created_date, 'DY') IN ('SAT', 'SUN')

Answer 3

@Joe Stefanelli This is not absolutely correct, since the abbreviations returned are NLS-sensitive: the language in which month and day names and abbreviations are returned use the default date language for your session.

If you compare to-char result to literals in English (IN ('SAT', 'SUN')), it's better to precisely indicate it with nlsparams parameter, so the resulting query will look like this:

SELECT *
    FROM YourTable
    WHERE  TO_CHAR (created_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT', 'SUN')