Oracle date function for the previous month

Question

I have the query below where the date is hard-coded  My objective is to remove the harcoded date  the query should pull the data for the previous month when it runs   select count distinct switch id    from xx new xx cti call details appsread prd com  where dealer name     XXXX     and TRUNC CREATION DATE  BETWEEN  01-AUG-2012  AND  31-AUG-2012    Should I use sysdate-15 function for that

User · Answer

Data for last month-  select count distinct switch id    from xx new xx cti call details appsread prd com  where dealer name     XXXX     and to char CREATION DATE  MMYYYY     to char add months trunc sysdate  -1   MMYYYY

User · Answer

The trunc   function truncates a date to the specified time period  so trunc sysdate  mm   would return the beginning of the current month  You can then use the add months   function to get the beginning of the previous month  something like this   select count distinct switch id       from xx new xx cti call details appsread prd com    where dealer name     XXXX         and creation date  gt   add months trunc sysdate  mm   -1      and creation date  lt  trunc sysdate   mm     As a little side not you re not explicitly converting to a date in your original query  Always do this  either using a date literal  e g  DATE 2012-08-31  or the to date   function  for example to date  2012-08-31   YYYY-MM-DD    If you don t then you are bound to get this wrong at some point   You would not use sysdate - 15 as this would provide the date 15 days before the current date  which does not seem to be what you are after  It would also include a time component as you are not using trunc       Just as a little demonstration of what trunc  lt date gt   mm   does   select sysdate        case when trunc sysdate  mm    gt  to date  20120901 00 00 00   yyyymmdd hh24 mi ss                then 1 end as gt        case when trunc sysdate  mm    lt  to date  20120901 00 00 00   yyyymmdd hh24 mi ss                then 1 end as lt        case when trunc sysdate  mm     to date  20120901 00 00 00   yyyymmdd hh24 mi ss                then 1 end as eq   from dual           SYSDATE                   GT         LT         EQ ----------------- ---------- ---------- ---------- 20120911 19 58 51                                1

User · Answer

It is working with me in Oracle sql developer     SELECT add months trunc sysdate  mm    -1              last day add months trunc sysdate  mm    -1        FROM dual

User · Answer

Modifying Ben s query little bit    select count distinct switch id       from xx new xx cti call details appsread prd com    where dealer name     XXXX         and creation date between add months trunc sysdate  mm   -1  and last day add months trunc sysdate  mm   -1

User · Answer

I believe this would also work   select count distinct switch id       from xx new xx cti call details appsread prd com    where     dealer name     XXXX         and  creation date BETWEEN add months trunc sysdate  mm   -1  and  trunc sysdate   mm      It has the advantage of using BETWEEN which is the way the OP used his date selection criteria

User · Answer

Getting last nth months data retrieve  SELECT   FROM TABLE NAME  WHERE DATE COLUMN BETWEEN   amp STARTDATE  AND   amp ENDDATE

[sql] Oracle date function for the previous month

Examples related to sql

Examples related to oracle

Examples related to sql-date-functions