Retrieving Weekend Data Rows Only in Oracle

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

Thanks

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

@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')

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

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