sql - Previous Weekdays -
i have requirement in have find start , end date. start date first sat of previous month of created date , end date previous friday of created date. eg below .. passing created date , need derive start , end date below.
created_dt start_date end_date 04/08/2015 15:36 04/07/2015 00:00 31/07/2015 23:59 07/07/2015 15:32 06/06/2015 00:00 03/07/2015 23:59
you should not depend on locale-specific nls settings.
you use following functions:
- next_day
- add_months
- trunc
for example,
sql> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss'; sql> t(created_dt) as( 2 select to_date('04/08/2015 15:36','dd/mm/yyyy hh24:mi') dual union 3 select to_date('07/07/2015 15:32','dd/mm/yyyy hh24:mi') dual 4 ) 5 select created_dt, 6 next_day(trunc(add_months(created_dt, -1),'mm') -1,to_char(to_date('6','j'),'day')) -1 start_date, 7 next_day(trunc(created_dt, 'mm') -1, to_char(to_date('5','j'),'day')) -1 + 0.99999 end_date 8 t; created_dt start_date end_date ------------------- ------------------- ------------------- 04/08/2015 15:36:00 04/07/2015 00:00:00 31/07/2015 23:59:59 07/07/2015 15:32:00 06/06/2015 00:00:00 03/07/2015 23:59:59 sql> to time portion 23:59:59, either add 0.99999 or subtract interval '1' second. example,
sql> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss'; session altered. sql> t(created_dt) as( 2 select to_date('04/08/2015 15:36','dd/mm/yyyy hh24:mi') dual union 3 select to_date('07/07/2015 15:32','dd/mm/yyyy hh24:mi') dual 4 ) 5 select created_dt, 6 next_day(trunc(add_months(created_dt, -1),'mm') -1,to_char(to_date('6','j'),'day')) -1 start_date, 7 next_day(trunc(created_dt, 'mm') -1, to_char(to_date('5','j'),'day')) - (interval '1' second) end_date 8 t; created_dt start_date end_date ------------------- ------------------- ------------------- 04/08/2015 15:36:00 04/07/2015 00:00:00 31/07/2015 23:59:59 07/07/2015 15:32:00 06/06/2015 00:00:00 03/07/2015 23:59:59 sql> 
Comments
Post a Comment