oracle时间处理

1将悉尼时间转换成洛杉矶时间

select from_tz(cast(to_date('2009-08-14 11:25:00', 'YYYY-MM-DD HH:MI:SS') as
                    timestamp),
               'Australia/Sydney') at time zone 'America/Los_Angeles' "Date and Time in LA"
  from dual;

2使用系统值把当前时间转换成为其它时区的任何时间

select systimestamp at time zone 'Australia/Sydney' "current time in sydney",

systimestamp at time zone 'America/Los_Angeles' "current Time in los angeles" from dual;

3检测闰年

select to_number(to_char(sysdate,'YYYY')) Year,case

when mod(to_number(to_char(sysdate,'YYYY')),400) = 0 then 'Leap YEAR'

when mod(to_number(to_char(sysdate,'YYYY')),100) = 0 then 'not leap year'

when mod(to_number(to_char(sysdate,'YYYY')),4) = 0 then 'leap year'

else as 'leap year?"

from dual;

4查看月份的最后一天

select last_day(sysdate) from dual;

5确定一个月的第一天

select trunc(sysdate,'mm') from dual;

确定这个季度的第一天

select trunc(sysdate,'Q') from dual;

6查看星期几

SQL> select to_char(sysdate,'day') from dual;


TO_CHAR(SYSDATE,'DAY')
------------------------
星期一

7确定今天是当月的第几周

SQL> select to_char(sysdate,'W') from dual;