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-04-11 12:34:56' d from dual
)
select d
from dates| D | 01-JAN-15 12.00.00.000000 AM | 10-JAN-15 12.00.00.000000 AM | 01-FEB-15 12.00.00.000000 AM | 11-APR-15 12.34.56.000000 PM |
|---|
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-04-11 12:34:56' d from dual
)
select trunc(d) nearest_day,
trunc(d, 'ww') nearest_week,
trunc(d, 'mm') nearest_month,
trunc(d, 'year') start_of_year
from dates| NEAREST_DAY | NEAREST_WEEK | NEAREST_MONTH | START_OF_YEAR | 01-JAN-15 | 01-JAN-15 | 01-JAN-15 | 01-JAN-15 | 10-JAN-15 | 08-JAN-15 | 01-JAN-15 | 01-JAN-15 | 01-FEB-15 | 29-JAN-15 | 01-FEB-15 | 01-JAN-15 | 11-APR-15 | 09-APR-15 | 01-APR-15 | 01-JAN-15 |
|---|
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-04-11 12:34:56' d from dual
)
select d original_date,
trunc(d) nearest_day,
trunc(d, 'ww') nearest_week,
trunc(d, 'mm') nearest_month,
trunc(d, 'year') start_of_year
from dates| ORIGINAL_DATE | NEAREST_DAY | NEAREST_WEEK | NEAREST_MONTH | START_OF_YEAR | 01-JAN-15 12.00.00.000000 AM | 01-JAN-15 | 01-JAN-15 | 01-JAN-15 | 01-JAN-15 | 10-JAN-15 12.00.00.000000 AM | 10-JAN-15 | 08-JAN-15 | 01-JAN-15 | 01-JAN-15 | 01-FEB-15 12.00.00.000000 AM | 01-FEB-15 | 29-JAN-15 | 01-FEB-15 | 01-JAN-15 | 11-APR-15 12.34.56.000000 PM | 11-APR-15 | 09-APR-15 | 01-APR-15 | 01-JAN-15 |
|---|
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-04-11 12:34:56' d from dual
)
select d original_date,
trunc(d) time_removed,
trunc(d, 'iw') start_of_week,
trunc(d, 'mm') start_of_month,
trunc(d, 'year') start_of_year
from dates| ORIGINAL_DATE | TIME_REMOVED | START_OF_WEEK | START_OF_MONTH | START_OF_YEAR | 01-JAN-15 12.00.00.000000 AM | 01-JAN-15 | 29-DEC-14 | 01-JAN-15 | 01-JAN-15 | 10-JAN-15 12.00.00.000000 AM | 10-JAN-15 | 05-JAN-15 | 01-JAN-15 | 01-JAN-15 | 01-FEB-15 12.00.00.000000 AM | 01-FEB-15 | 26-JAN-15 | 01-FEB-15 | 01-JAN-15 | 11-APR-15 12.34.56.000000 PM | 11-APR-15 | 06-APR-15 | 01-APR-15 | 01-JAN-15 |
|---|
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, 'mi'),
trunc(d) time_removed,
trunc(d, 'iw') start_of_week,
trunc(d, 'mm') start_of_month,
trunc(d, 'year') start_of_year
from dates| ORIGINAL_DATE | TRUNC(D,'MI') | TIME_REMOVED | 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 | 10-JAN-15 | 05-JAN-15 | 01-JAN-15 | 01-JAN-15 | 01-FEB-15 12.00.00.000000 AM | 01-FEB-15 | 01-FEB-15 | 26-JAN-15 | 01-FEB-15 | 01-JAN-15 | 03-MAR-15 11.45.00.000000 PM | 03-MAR-15 | 03-MAR-15 | 02-MAR-15 | 01-MAR-15 | 01-JAN-15 | 11-APR-15 12.34.56.000000 PM | 11-APR-15 | 11-APR-15 | 06-APR-15 | 01-APR-15 | 01-JAN-15 |
|---|
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) 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 | 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 |
|---|
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi'Statement processed.
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) 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 | 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 |
|---|
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
)
select to_char(d, 'dd-mm-yyyy'),
to_char(d, 'hh24:mi'),
to_char(d, 'iw-iyyy')
from dates| TO_CHAR(D,'DD-MM-YYYY') | TO_CHAR(D,'HH24:MI') | TO_CHAR(D,'IW-IYYY') | 01-01-2015 | 00:00 | 01-2015 | 10-01-2015 | 00:00 | 02-2015 | 01-02-2015 | 00:00 | 05-2015 |
|---|
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 to_char(d, 'dd-mm-yyyy') day_month_year,
to_char(d, 'hh24:mi') time_only,
to_char(d, 'iw-iyyy') iso_week_and_year,
to_char(d, 'iw-iyyy') iso_week_and_year
from dates| DAY_MONTH_YEAR | TIME_ONLY | ISO_WEEK_AND_YEAR | ISO_WEEK_AND_YEAR | 01-01-2015 | 00:00 | 01-2015 | 01-2015 | 10-01-2015 | 00:00 | 02-2015 | 02-2015 | 01-02-2015 | 00:00 | 05-2015 | 05-2015 | 03-03-2015 | 23:44 | 10-2015 | 10-2015 | 11-04-2015 | 12:34 | 15-2015 | 15-2015 |
|---|
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 to_char(d, 'dd-mm-yyyy') day_month_year,
to_char(d, 'hh24:mi') time_only,
to_char(d, 'iw-iyyy') iso_week_and_year,
to_char(d, 'Month') month_name
from dates| DAY_MONTH_YEAR | TIME_ONLY | ISO_WEEK_AND_YEAR | MONTH_NAME | 01-01-2015 | 00:00 | 01-2015 | January | 10-01-2015 | 00:00 | 02-2015 | January | 01-02-2015 | 00:00 | 05-2015 | February | 03-03-2015 | 23:44 | 10-2015 | March | 11-04-2015 | 12:34 | 15-2015 | April |
|---|
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 to_char(d, 'dd-mm-yyyy') day_month_year,
to_char(d, 'hh24:mi') time_only_24hour,
to_char(d, 'iw-iyyy') iso_week_and_year,
to_char(d, 'Month') month_name,
to_char(d, 'Year') year
from dates| DAY_MONTH_YEAR | TIME_ONLY_24HOUR | ISO_WEEK_AND_YEAR | MONTH_NAME | YEAR | 01-01-2015 | 00:00 | 01-2015 | January | Twenty Fifteen | 10-01-2015 | 00:00 | 02-2015 | January | Twenty Fifteen | 01-02-2015 | 00:00 | 05-2015 | February | Twenty Fifteen | 03-03-2015 | 23:44 | 10-2015 | March | Twenty Fifteen | 11-04-2015 | 12:34 | 15-2015 | April | Twenty Fifteen |
|---|
with nums as (
select 10 n from dual union
select 9.99 n from dual union
select 1000000 n from dual --one million
)
select n,
to_char(n, '9,999,999.99'),
to_char(n),
to_char(n, '0,000,000.000')
from nums| N | TO_CHAR(N,'9,999,999.99') | TO_CHAR(N) | TO_CHAR(N,'0,000,000.000') | 9.99 | 9.99 | 9.99 | 0,000,009.990 | 10 | 10.00 | 10 | 0,000,010.000 | 1000000 | 1,000,000.00 | 1000000 | 1,000,000.000 |
|---|
with nums as (
select 10 n from dual union
select 9.99 n from dual union
select 1000000 n from dual --one million
)
select n,
to_char(n),
to_char(n, '9,999,999.99') show_commas,
to_char(n, '0,000,000.000') zero_padded,
to_char(n, '9.9EEEE')
from nums| N | TO_CHAR(N) | SHOW_COMMAS | ZERO_PADDED | TO_CHAR(N,'9.9EEEE') | 9.99 | 9.99 | 9.99 | 0,000,009.990 | 1.0E+01 | 10 | 10 | 10.00 | 0,000,010.000 | 1.0E+01 | 1000000 | 1000000 | 1,000,000.00 | 1,000,000.000 | 1.0E+06 |
|---|
with nums as (
select 10 n from dual union
select 9.99 n from dual union
select 1000000 n from dual --one million
)
select n,
to_char(n),
to_char(n, '9,999,999.99') show_commas,
to_char(n, '0,000,000.000') zero_padded,
to_char(n, '9.9EEEE') scientific_notation,
to_char(n, '$9,999,999.99') monetary
from nums| N | TO_CHAR(N) | SHOW_COMMAS | ZERO_PADDED | SCIENTIFIC_NOTATION | MONETARY | 9.99 | 9.99 | 9.99 | 0,000,009.990 | 1.0E+01 | $9.99 | 10 | 10 | 10.00 | 0,000,010.000 | 1.0E+01 | $10.00 | 1000000 | 1000000 | 1,000,000.00 | 1,000,000.000 | 1.0E+06 | $1,000,000.00 |
|---|
with nums as (
select 10 n from dual union
select 9.99 n from dual union
select .99 n from dual union
select 1000000 n from dual --one million
)
select n,
to_char(n),
to_char(n, '9,999,999.99') show_commas,
to_char(n, '0,000,000.000') zero_padded,
to_char(n, '9.9EEEE') scientific_notation,
to_char(n, '$9,999,990.00') monetary
from nums| N | TO_CHAR(N) | SHOW_COMMAS | ZERO_PADDED | SCIENTIFIC_NOTATION | MONETARY | .99 | .99 | .99 | 0,000,000.990 | 9.9E-01 | $0.99 | 9.99 | 9.99 | 9.99 | 0,000,009.990 | 1.0E+01 | $9.99 | 10 | 10 | 10.00 | 0,000,010.000 | 1.0E+01 | $10.00 | 1000000 | 1000000 | 1,000,000.00 | 1,000,000.000 | 1.0E+06 | $1,000,000.00 |
|---|
with nums as (
select 10 n from dual union
select 9.99 n from dual union
select .99 n from dual union
select 1000000 n from dual --one million
)
select n,
to_char(n),
to_char(n, '9,999,999.99') show_commas,
to_char(n, '0,000,000.000') zero_padded,
to_char(n, '9.9EEEE') scientific_notation,
to_char(n, '$9,999,990.00') monetary,
to_char(n, 'X') hexadecimal_value
from nums| N | TO_CHAR(N) | SHOW_COMMAS | ZERO_PADDED | SCIENTIFIC_NOTATION | MONETARY | HEXADECIMAL_VALUE | .99 | .99 | .99 | 0,000,000.990 | 9.9E-01 | $0.99 | 1 | 9.99 | 9.99 | 9.99 | 0,000,009.990 | 1.0E+01 | $9.99 | A | 10 | 10 | 10.00 | 0,000,010.000 | 1.0E+01 | $10.00 | A | 1000000 | 1000000 | 1,000,000.00 | 1,000,000.000 | 1.0E+06 | $1,000,000.00 | ## |
|---|
with nums as (
select 10 n from dual union
select 9.99 n from dual union
select .99 n from dual union
select 1000000 n from dual --one million
)
select n,
to_char(n),
to_char(n, '9,999,999.99') show_commas,
to_char(n, '0,000,000.000') zero_padded,
to_char(n, '9.9EEEE') scientific_notation,
to_char(n, '$9,999,990.00') monetary,
to_char(n, 'XXXXX') hexadecimal_value
from nums| N | TO_CHAR(N) | SHOW_COMMAS | ZERO_PADDED | SCIENTIFIC_NOTATION | MONETARY | HEXADECIMAL_VALUE | .99 | .99 | .99 | 0,000,000.990 | 9.9E-01 | $0.99 | 1 | 9.99 | 9.99 | 9.99 | 0,000,009.990 | 1.0E+01 | $9.99 | A | 10 | 10 | 10.00 | 0,000,010.000 | 1.0E+01 | $10.00 | A | 1000000 | 1000000 | 1,000,000.00 | 1,000,000.000 | 1.0E+06 | $1,000,000.00 | F4240 |
|---|
with nums as (
select 10 n from dual union
select 9.99 n from dual union
select .99 n from dual union
select 1000000 n from dual --one million
)
select n,
to_char(n),
to_char(n, '9,999,999.99') show_commas,
to_char(n, '0,000,000.000') zero_padded,
to_char(n, '9.9EEEE') scientific_notation,
to_char(n, '$9,999,990.00') monetary,
to_char(n, 'XXXXXX') hexadecimal_value
from nums| N | TO_CHAR(N) | SHOW_COMMAS | ZERO_PADDED | SCIENTIFIC_NOTATION | MONETARY | HEXADECIMAL_VALUE | .99 | .99 | .99 | 0,000,000.990 | 9.9E-01 | $0.99 | 1 | 9.99 | 9.99 | 9.99 | 0,000,009.990 | 1.0E+01 | $9.99 | A | 10 | 10 | 10.00 | 0,000,010.000 | 1.0E+01 | $10.00 | A | 1000000 | 1000000 | 1,000,000.00 | 1,000,000.000 | 1.0E+06 | $1,000,000.00 | F4240 |
|---|
REM set dates to display as 01-Jan-2015 00:00 set dates to display as 01-Jan-2015 00:00
alter session set nls_date_format = 'dd-Mon-yyyy hh24:mi'Statement processed.
REM Trunc on dates Trunc on 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:44:32' d from dual union
select timestamp'2015-04-11 12:34:56' d from dual
)
select d original_date,
trunc(d) time_removed,
trunc(d, '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 | 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 |
|---|
REM to_char on dates to_char on 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:44:32' d from dual union
select timestamp'2015-04-11 12:34:56' d from dual
)
select to_char(d, 'dd-mm-yyyy') day_month_year,
to_char(d, 'hh24:mi') time_only_24hour,
to_char(d, 'iw-iyyy') iso_week_and_year,
to_char(d, 'Month') month_name,
to_char(d, 'Year') year_in_words
from dates| DAY_MONTH_YEAR | TIME_ONLY_24HOUR | ISO_WEEK_AND_YEAR | MONTH_NAME | YEAR_IN_WORDS | 01-01-2015 | 00:00 | 01-2015 | January | Twenty Fifteen | 10-01-2015 | 00:00 | 02-2015 | January | Twenty Fifteen | 01-02-2015 | 00:00 | 05-2015 | February | Twenty Fifteen | 03-03-2015 | 23:44 | 10-2015 | March | Twenty Fifteen | 11-04-2015 | 12:34 | 15-2015 | April | Twenty Fifteen |
|---|
REM to_char on numbers to_char on numbers
with nums as (
select 10 n from dual union
select 9.99 n from dual union
select .99 n from dual union
select 1000000 n from dual --one million
)
select n,
to_char(n),
to_char(n, '9,999,999.99') show_commas,
to_char(n, '0,000,000.000') zero_padded,
to_char(n, '9.9EEEE') scientific_notation,
to_char(n, '$9,999,990.00') monetary,
to_char(n, 'XXXXXXX') hexadecimal_value
from nums| N | TO_CHAR(N) | SHOW_COMMAS | ZERO_PADDED | SCIENTIFIC_NOTATION | MONETARY | HEXADECIMAL_VALUE | .99 | .99 | .99 | 0,000,000.990 | 9.9E-01 | $0.99 | 1 | 9.99 | 9.99 | 9.99 | 0,000,009.990 | 1.0E+01 | $9.99 | A | 10 | 10 | 10.00 | 0,000,010.000 | 1.0E+01 | $10.00 | A | 1000000 | 1000000 | 1,000,000.00 | 1,000,000.000 | 1.0E+06 | $1,000,000.00 | F4240 |
|---|
REM Extract examples to get components of a date
Extract examples to get components of a date
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 extract(minute from d) minutes,
extract(hour from d) hours,
extract(day from d) days,
extract(month from d) months,
extract(year from d) years
from 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:44:32' d from dual union
select timestamp'2015-04-11 12:34:56' d from dual
)
select extract(minute from d) minutes,
extract(hour from d) hours,
extract(day from d) days,
extract(month from d) months,
extract(year from d) years
from dates| MINUTES | HOURS | DAYS | MONTHS | YEARS | 0 | 0 | 1 | 1 | 2015 | 0 | 0 | 10 | 1 | 2015 | 0 | 0 | 1 | 2 | 2015 | 44 | 23 | 3 | 3 | 2015 | 34 | 12 | 11 | 4 | 2015 |
|---|
REM sysdate - returns the current date and time sysdate - returns the current date and time
select sysdate
from dual| SYSDATE | 10-APR-15 |
|---|
REM You can find the elapsed days between today and the start of the year by You can find the elapsed days between today and the start of the year by
REM Just subtract sysdate truncated to the year from sysdate Just subtract sysdate truncated to the year from sysdate
select sysdate - trunc(sysdate, 'y')
from dual| SYSDATE-TRUNC(SYSDATE,'Y') | 99.26763888888888888888888888888888888889 |
|---|