数据库的时间运算
oracle
--取现在时间
select sysdate from dual --2010-7-15 10:51:31
--前两天，默认以天为单位
select sysdate-2 from dual --2010-7-13 10:51:31
--前一小时
select sysdate-1/24 from dual --2010-7-15 9:51:31
--前两个月
select add_months(sysdate,-2) from dual --2010-5-15 10:51:31
--字符串转日期
select to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss') from dual --2010-7-15 10:51:31
select to_date('2010-7-15','yyyy-mm-dd') from dual --2010-7-15
--日期转字符串
select to_char(sysdate,'yyyy') from dual --2010
select to_char(to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss'),'yyyy') from dual --2010
select to_char(to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss'),'year') from dual --twenty ten
select to_char(to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss'),'mm') from dual --07
select to_char(to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss'),'month') from dual --7月
select to_char(to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss'),'dd') from dual --15
select to_char(to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss'),'day') from dual --星期四
select to_char(to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss'),'hh24') from dual --10
select to_char(to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss'),'mi') from dual --51
select to_char(to_date('2010-7-15 10:51:31','yyyy-mm-dd hh24:mi:ss'),'ss') from dual --31
--求时间差
select to_date('2010-07-14 9:12:35','yyyy-mm-dd hh24:mi:ss') -
to_date('2010-07-13 15:12:35','yyyy-mm-dd hh24:mi:ss') from dual --0.75，默认以天为单位
select (to_date('2010-07-14 9:12:35','yyyy-mm-dd hh24:mi:ss') -
to_date('2010-07-13 15:12:35','yyyy-mm-dd hh24:mi:ss'))*24 from dual --18，转化为小时
select (to_date('2010-07-14 9:12:35','yyyy-mm-dd hh24:mi:ss') -
to_date('2010-07-13 15:12:36','yyyy-mm-dd hh24:mi:ss'))*24 from dual --17.9997222222222，与分秒有关
--选出2010-7-13 15:30:00之后的新闻
select NewsId,NewsTitle from BS_News where CreateTime >= to_date('2010-7-13 15:30:00','yyyy-mm-dd hh24:mi:ss')
--选出2010-7-13 00:00:00之后的新闻
select NewsId,NewsTitle from BS_News where CreateTime >= to_date('2010-7-13 00:00:00','yyyy-mm-dd hh24:mi:ss')
--等价于：select NewsId,NewsTitle from BS_News where CreateTime >= to_date('2010-7-13','yyyy-mm-dd')
--选出2010.07.12的新闻
select NewsId,NewsTitle from BS_News where CreateTime between to_date('2010-07-12','yyyy-mm-dd') and to_date('2010-07-12
23:59:59','yyyy-mm-dd hh24:mi:ss')
sql
--取现在时间
select getdate() --2010-07-13 10:51:31.793
--前两天
select dateadd(dd,-2,getdate()) --2010-07-13 10:51:31.793
--前一小时
select dateadd(hh,-1,getdate()) --2010-07-15 9:51:31.793
--前两个月
select dateadd(mm,-2,getdate()) --2010-05-15 10:51:31.793
--字符串转日期
select cast('2010-07-15 10:51:31.793' as datetime) --2010-07-15 10:51:31.793
--日期转字符串
select cast(getdate() as varchar(18)) --07 15 2010 10:51AM
select convert(varchar(18),getdate()) --07 15 2010 10:51AM
--转成更多字符串格式
Select convert(varchar(19),getdate(), 20) --2010-07-15 10:51:31
Select convert(varchar(10),getdate(), 111) --2010/07/15
--求时间差
--精确到年份，2010-2009=1，与月/日无关
select datediff(yyyy, '2009-07-14', '2010-01-20')
--精确到小时，11-9+24=26，与分/秒无关
select datediff(hh,'2010-07-14 9:12:35.012','2010-07-15 11:50:35.012')
--类似的date-part单位还有mm-月，dd-日，mi-分钟，ss-秒，mi-毫秒
--选出2010-7-13 15:30:00之后的新闻
select NewsId,NewsTitle from BS_News where CreateTime >= cast('2010-7-13 15:30:00' as datetime)
--选出2010-7-13 00:00:00之后的新闻
select NewsId,NewsTitle from BS_News where CreateTime >= cast('2010-7-13 00:00:00' as datetime)
--等价于：select NewsId,NewsTitle from BS_News where CreateTime >= cast('2010-7-13' as datetime)
--选出2010.07.12的新闻
select NewsId,NewsTitle from BS_News where CreateTime between cast('2010-07-12' as datetime) and cast('2010-07-12 23:59:59'
as datetime)
