Run a query based on weekends in oracle

Question

am new to ORACLE; please help Am trying to run a query which pulls up records based on dates;

if date is monday the query should run thrice; that is for monday, sunday, saturday (previous days also) for others days (tue to thursday only for same day);

Why does the subject say oracle and it's tagged for SQL server? There's a big difference in date handling between them — James Z, Jun 08 '15 at 02:47
Your title says Oracle but your tag is SQL Server. Please clarify which RDBMS you are working with. — DeanOC, Jun 08 '15 at 02:47

score 0 · Answer 1 · edited Jun 08 '15 at 04:09

First, if you want to run the query three times you'll need to do that in something other than plain SQL: PL-SQL, Java, C#, something.

However if you'd like Saturday and Sunday's figures to be included in Monday's you'll need to do something like a CASE statement to change the Sats & Suns to Mons.

 case when (trim(to_char((date), 'Day', 'NLS_DATE_LANGUAGE=ENGLISH'))
  in ('Saturday', 'Sunday', 'Monday')) then 'Monday'
  else trim(to_char((date), 'Day', 'NLS_DATE_LANGUAGE=ENGLISH')) 
 end

score 0 · Answer 2 · answered Jun 08 '15 at 04:48

Am trying to run a query which pulls up records based on dates

For scheduling based on calendering, Oracle provides DBMS_SCHEDULER.

For example, the below job would execute every hour:

SQL> BEGIN
  2    DBMS_SCHEDULER.DROP_JOB (JOB_NAME => 'test_full_job_definition');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    DBMS_SCHEDULER.create_job (
  3      job_name        => 'test_full_job_definition',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'BEGIN my_job_procedure; END;',
  6      start_date      => SYSTIMESTAMP,
  7      repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
  8      end_date        => NULL,
  9      enabled         => TRUE,
 10      comments        => 'Job defined entirely by the CREATE JOB procedure.');
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT JOB_NAME, ENABLED FROM DBA_SCHEDULER_JOBS where job_name ='TEST_FULL_JOB_DEFINITION'
  2  /

JOB_NAME                                 ENABL
---------------------------------------- -----
TEST_FULL_JOB_DEFINITION                 TRUE

SQL>

More examples here

Run a query based on weekends in oracle

2 Answers2