Oracle日期与时间

 

1       char,date相互转换

create table ly_test(name varchar(32), birthday date, son_birthday varchar(32));

insert into ly_test values ('liuyou', sysdate, ‘2007-04-27 11:10:08’);

SQL> select * from ly_test;

 

NAME                             BIRTHDAY    SON_BIRTHDAY

-------------------------------- ----------- --------------------------------

liuyou                           2007-4-27 1 2007-04-27 11:10:08

SQL> select to_char(birthday, yyyy-MM-dd hh24:mi:ss')  now from ly_test;

 

NOW

------------------------------

2007-04-27 17:56:22

 

2       日期差

oracle是以天为单位的，涉及到小时，分，秒要除以24，24*60, 24*60*60；

 

1． 从ly_test表中找出20天前过生日的记录

select * from ly_test where birthday<sysdate-20;

2． 从ly_test表中找出明天要过生日的记录

select * from ly_test where birthday>to_date(to_char(sysdate+1, ‘yyyy-MM-dd’), ‘yyyy-MM-dd’);