Oracle sysdate / timestamp etc.

This just a reminder to myself when outputting date / time  in oracle.

Loads of possibilities to achieve this - one solution.

Standard sysdate output (limited to date only but pain when you want the time too)...

SQL> select sysdate from dual;

SYSDATE
---------
07-JAN-19

Could use the cast function to change the type to timestamp so more detail to include time

SQL> select CAST(sysdate as timestamp) from dual;

CAST(SYSDATEASTIMESTAMP)
---------------------------------------------------------------------------
07-JAN-19 09.37.41.000000 AM

more control alter session and pick the format you want....

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
07-JAN-2019 09:37:54

Break a table down into counts by month for a date column :

SQL> select count(REQ_TIMESTAMP), to_char(trunc REQ_TIMESTAMP),'MM-YYYY') from REQ_LOG group by to_char(trunc(REQ_TIMESTAMP),'MM-YYYY');

COUNT(REQUEST_TIMESTAMP) TO_CHAR
------------------------ -------

2740644  04-2019
3978        02-2019
3604984  05-2019

or...

SQL> select count(*),trunc(REQ_TIMESTAMP,'MM') from REQ_LOG group by trunc(REQ_TIMESTAMP,'MM');

COUNT(*) TRUNC(INS
---------- ---------
2740644  01-APR-19
3978        01-FEB-19
3604984  01-MAY-19

SQL> delete from EC_MAIN.API_REQUEST_LOG where trunc(INSERTED_DTM,'MM') = '01-FEB-19';

3978 rows deleted.