Truncate input date values
WITH dates AS (
SELECT date'2015-01-01' d FROM dual union
SELECT date'2015-01-10' d FROM dual union
SELECT date'2015-02-01' d FROM dual union
SELECT timestamp'2015-03-03 23:45:00' d FROM dual union
SELECT timestamp'2015-04-11 12:34:56' d FROM dual
)
SELECT d "Original Date",
trunc(d) "Nearest Day, Time Removed",
trunc(d, 'ww') "Nearest Week",
trunc(d, 'iw') "Start of Week",
trunc(d, 'mm') "Start of Month",
trunc(d, 'year') "Start of Year"
FROM dates| Original Date | Nearest Day, Time Removed | Nearest Week | Start of Week | Start of Month | Start of Year |
|---|---|---|---|---|---|
| 01-JAN-15 12.00.00.000000 AM | 01-JAN-15 | 01-JAN-15 | 29-DEC-14 | 01-JAN-15 | 01-JAN-15 |
| 10-JAN-15 12.00.00.000000 AM | 10-JAN-15 | 08-JAN-15 | 05-JAN-15 | 01-JAN-15 | 01-JAN-15 |
| 01-FEB-15 12.00.00.000000 AM | 01-FEB-15 | 29-JAN-15 | 26-JAN-15 | 01-FEB-15 | 01-JAN-15 |
| 03-MAR-15 11.45.00.000000 PM | 03-MAR-15 | 26-FEB-15 | 02-MAR-15 | 01-MAR-15 | 01-JAN-15 |
| 11-APR-15 12.34.56.000000 PM | 11-APR-15 | 09-APR-15 | 06-APR-15 | 01-APR-15 | 01-JAN-15 |
Use TO_CHAR to display individual components of truncated dates
WITH dates AS (
SELECT date'2015-01-01' d FROM dual union
SELECT date'2015-01-10' d FROM dual union
SELECT date'2015-02-01' d FROM dual union
SELECT timestamp'2015-03-03 23:45:00' d FROM dual union
SELECT timestamp'2015-04-11 12:34:56' d FROM dual
)
SELECT d "Original Date",
trunc(d) "Date with Time Removed",
to_char(trunc(d, 'mi'), 'dd-mon-yyyy hh24:mi') "Nearest Minute",
trunc(d, 'iw') "Start of Week",
trunc(d, 'mm') "Start of Month",
trunc(d, 'year') "Start of Year"
FROM dates| Original Date | Date with Time Removed | Nearest Minute | Start of Week | Start of Month | Start of Year |
|---|---|---|---|---|---|
| 01-JAN-15 12.00.00.000000 AM | 01-JAN-15 | 01-jan-2015 00:00 | 29-DEC-14 | 01-JAN-15 | 01-JAN-15 |
| 10-JAN-15 12.00.00.000000 AM | 10-JAN-15 | 10-jan-2015 00:00 | 05-JAN-15 | 01-JAN-15 | 01-JAN-15 |
| 01-FEB-15 12.00.00.000000 AM | 01-FEB-15 | 01-feb-2015 00:00 | 26-JAN-15 | 01-FEB-15 | 01-JAN-15 |
| 03-MAR-15 11.45.00.000000 PM | 03-MAR-15 | 03-mar-2015 23:45 | 02-MAR-15 | 01-MAR-15 | 01-JAN-15 |
| 11-APR-15 12.34.56.000000 PM | 11-APR-15 | 11-apr-2015 12:34 | 06-APR-15 | 01-APR-15 | 01-JAN-15 |
Set NLS_DATE_FORMAT for current session
ALTER SESSION SET nls_date_format = 'dd-mon-yyyy hh24:mi'Statement processed.
Display previous date output after changing NLS_DATE_FORMAT for curent session
WITH dates AS (
SELECT date'2015-01-01' d FROM dual union
SELECT date'2015-01-10' d FROM dual union
SELECT date'2015-02-01' d FROM dual union
SELECT timestamp'2015-03-03 23:44:32' d FROM dual union
SELECT timestamp'2015-04-11 12:34:56' d FROM dual
)
SELECT d "Original Date",
trunc(d) "Date, time removed",
to_char(trunc(d, 'mi'), 'dd-mon-yyyy hh24:mi') "Nearest Minute",
trunc(d, 'iw') "Start of Week",
trunc(d, 'mm') "Start of Month",
trunc(d, 'year') "Start of Year"
FROM dates| Original Date | Date, time removed | Nearest Minute | Start of Week | Start of Month | Start of Year |
|---|---|---|---|---|---|
| 01-JAN-15 12.00.00.000000 AM | 01-jan-2015 00:00 | 01-jan-2015 00:00 | 29-dec-2014 00:00 | 01-jan-2015 00:00 | 01-jan-2015 00:00 |
| 10-JAN-15 12.00.00.000000 AM | 10-jan-2015 00:00 | 10-jan-2015 00:00 | 05-jan-2015 00:00 | 01-jan-2015 00:00 | 01-jan-2015 00:00 |
| 01-FEB-15 12.00.00.000000 AM | 01-feb-2015 00:00 | 01-feb-2015 00:00 | 26-jan-2015 00:00 | 01-feb-2015 00:00 | 01-jan-2015 00:00 |
| 03-MAR-15 11.44.32.000000 PM | 03-mar-2015 00:00 | 03-mar-2015 23:44 | 02-mar-2015 00:00 | 01-mar-2015 00:00 | 01-jan-2015 00:00 |
| 11-APR-15 12.34.56.000000 PM | 11-apr-2015 00:00 | 11-apr-2015 12:34 | 06-apr-2015 00:00 | 01-apr-2015 00:00 | 01-jan-2015 00:00 |