Re: Oracle SQL Date Calculations?

On 13 Jan 2006 10:33:41 -0800, "RK" <rekaben_at_yahoo.com> wrote:

>Hi, just like to have some quick command reference.
>
>By the following command:
>
>select to_date('20051231','YYYYMMDD') - to_date('20051101','YYYYMMDD')
>from dual;
>
>we can get the difference between the two dates are 60 days.
>
>By using add_months() we can get the month from given difference.
>
>Are there more such quick tools? I would need the following:
>
>Given a date for the day during a given week, output the start and end
>date(s) of that week;
>
>How about given day in a month? Year?
>
>Feburary's last day, or leap year or not?
>
>
>...
>
>
>Thanks.

1
select next_day(sysdate,'MONDAY')-7,
next_day(next_day(sysdate,'MONDAY')-7,'SUNDAY') from dual

2
select trunc(sysdate,'MONTH'), last_day(trunc(sysdate,'MONTH')) from dual

3 select trunc(sysdate,'YEAR'),
last_day(add_months(trunc(sysdate,'YEAR'),11)) from dual

4
 select
to_char(last_day(to_date('01-02-2008','dd-mm-yyyy')),'dd-mm-yyyyy'),  decode(to_char(last_day(to_date('01-02-2008','dd-mm-yyyy')),'dd'), 28,0,1) leapyear
  from dual

Nice interview questions!
I would suggest not hiring anyone who can't answer these questions!

--
Sybrand Bakker, Senior Oracle DBA