APEXblog.nl - Tips and Tricks

About This Blog:
I (Richard Weug) started this blog primary for myself to save all kinds of Apex tips and tricks in one place. To use it as a place to write down how I used some coding in my own projects, but also to copy and paste all kinds of articles I find on the Internet. (So I never have to wonder on what website did I read about??? When I see something interesting I collect the content so I have my own archive/knowlegde base.

View my profile on LinkedIn

 

PL/SQL stuff

Print
16
May

Fun with dates

Written by Richard Weug. Posted in PL/SQL

Here I'm just listing some fun with dates, either from single row sql expressions or cool little queries.

 

select 
  sysdate today
 ,trunc(sysdate) midnight_today
 ,trunc(sysdate)+1/86400 one_second
 ,trunc(sysdate)+2/24 two_hours
 ,trunc(sysdate)+30/60/24 thirty_minutes
 ,trunc(sysdate) + interval '2:30' hour to minute two_hour_thirty
 ,trunc(sysdate,'mm') start_of_month
 ,trunc(sysdate,'yy') start_of_year
 ,trunc(sysdate,'yy')+6 financial_year
 ,extract(year from sysdate) year_as_number
 ,to_char(sysdate,'yyyy') year_as_varchar2
 ,(sysdate - add_months(sysdate,6)) year to month  interval_months
 ,(Sysdate-(sysdate+2+30/60/24)) day(9) to second interval_daysec
 ,to_date('2012','yyyy') start_of_month
 ,last_day(sysdate) end_of_month
 ,date '2012-02-28' + 1 leap_year
 ,date '2011-02-28' + 1 non_leap_year
 ,date '2012-02-28' + interval '1' day only_during_leap_year
 ,date '1582-10-4' + 1 gregorian_changeover
 ,to_date('01-01-4712bc','dd-mm-yyyybc') scaliger_start
 ,to_date(1,'j') easier_scaliger_start
 ,to_date('31-12-9999','dd-mm-yyyy') end_of_time
 ,to_date('01-01-98','dd-mm-yy') legacy_wrong_century
 ,to_date('01-01-98','dd-mm-rr') legacy_better_format
from dual;

-- Generate first day of each month
select add_months(trunc(sysdate,'yy'),rownum-1) months
from dual connect by level <= 12;

MONTHS
---------------------
01/01/2012 00:00:00
01/02/2012 00:00:00
01/03/2012 00:00:00
01/04/2012 00:00:00
01/05/2012 00:00:00
01/06/2012 00:00:00
01/07/2012 00:00:00
01/08/2012 00:00:00
01/09/2012 00:00:00
01/10/2012 00:00:00
01/11/2012 00:00:00
01/12/2012 00:00:00

12 rows selected

 

-- Generate list of this year's weekends
select dt from (
  select trunc(sysdate,'yy')+rownum-1 dt
  from dual connect by level <= 365)
where to_char(dt,'dy') in ('sat','sun');

DT                  
---------------------
01/01/2012 00:00:00   
07/01/2012 00:00:00   
08/01/2012 00:00:00   
14/01/2012 00:00:00   
...
23/12/2012 00:00:00   
29/12/2012 00:00:00   
30/12/2012 00:00:00   

105 rows selected 

original article by Scott: Wesley

Link to article:  http://www.grassroots-oracle.com/2012/05/more-fun-with-dates.html