Reset part of the date to the first month, saving time

Question

Is there a way to reset parts of a date to the first month by saving time? For instance:

2018-01-02 23:00:00 -> 2018-01-01 23:00:00 2018-04-04 10:00:00 -> 2018-04-01 10:00:00 

Answer 1

Yes. For instance:

 select sysdate, sysdate + (trunc(sysdate, 'mm') - trunc(sysdate)) as other_date from dual; SYSDATE OTHER_DATE ------------------- ------------------- 2018-04-06 10:17:47 2018-04-01 10:17:47 

Obviously, there is no easy way to do this (you must recognize this as an unusual requirement).

Equivalent Arithmetic:

 trunc(sysdate, 'mm') + (sysdate - trunc(sysdate)) 

Use something that will be easier for you to understand right away. In both cases, what you have in parentheses after the + sign is the difference of two dates, which is the number (measured in days) that can be added to the date (the expression BEFORE the + sign).

Answer 2

 with x as ( select to_date( '2018-01-02 23:00:00', 'yyyy-mm-dd hh24:mi:ss') as d from dual union all select to_date( '2018-04-04 10:00:00', 'yyyy-mm-dd hh24:mi:ss') from dual ) SELECT d, d - trunc( d ) + trunc( d, 'MM' ) FROM x; D D-TRUNC(D)+TRUNC(D, ------------------- ------------------- 2018-01-02 23:00:00 2018-01-01 23:00:00 2018-04-04 10:00:00 2018-04-01 10:00:00 

Answer 3

Another solution:

 ts - ((extract(day from ts)-1))