- 确定一年是否是闰年
- 确定一年内的天数
- 从日期中提取时间的各部分
- 确定某个月的第一天和最后一天
- 确定一年内属于周内某天的所有日期
- 确定某月内第一个和最后一个周内某天的日期
- 创建日历
- 列出一年中每个季度的开始日期和结束日期
- 确定某个给定季度的开始日期和结束日期
- 填充丢失的日期
- 按照给定的时间单位进行查找
- 使用日期的特殊部分比较记录
- 识别重叠的日期范围
1.确定一年是否是闰年
方案：检查2月最后一天是29，确定是否是闰年
db2: with x(dy,mth) as (
select dy, month(dy) from (
select (current_date - dayofyear(current_date) days +1 days)+1 months as dy from t1
) tmp1 union all
select dy+1 days,mth from x where month(dy+1 day)=mth
)
select max(day(dy)) from x;
oracle : select to_char(last_day(add_months(tranc(sysdate,'y'),1)),'DD') from t1;
postgresql: select max(to_char(tmp2.dy+x.id,'DD')) as dy from (
select dy,to_char(dy,'MM') as mth from (
select cast(cast(date_tranc('year',current_date) as date)+ interval '1 month' as date) as dy from t1
)tmp1
)tmp2,generate_series(0,29) x(id)
where to_char(tmp2.dy+x.id,'MM') = tmp2.mth;
mysql: select day(last_day(date_add(date_add(date_add(current_date,interval -dayofyear(current_date) day),interval 1 day),interval 1 month))) dy from t1;
sqlserver: with x (dy,mth) as (
select dy,month(dy) from (
select dateadd(mm,1,(getdate()-datepart(dy,getdate()))+1) dy from t1
) tmp1 union all
select dateadd(dd,1,dy),mth from x where month(dateadd(dd,1,dy))=mth
)
select max(day(dy)) from x;
Note:估计会在存储过程中用吧
2.确定一年内的天数
db2: select day((curr_year+1 year)) - days(curr_year) from (
select (current_date -dayofyear(current_date) day +1 day) curr_year from t1
) x;
oracle: select add_months(trunc(sysdate,'y'),12) -tranc(sysdate,'y') from dual;
posgresql: select cast((curr_year + interval '1 year') as date) - curr_year from (
select cast(date_trunc('year',current_date) as date) as curr_year from t1
) x;
sqlserver: select datediff(d,curr_year,dateadd(yy,1,curr_year)) from (
select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year from t1
) x;
mysql: select datediff((curr_year + interval 1 year),curr_year) from (
select adddate(current_date,-dayofyear(current_date)+1) curr_year from t1
) x;
Note:我不是太确定这个在数据库中会不会用到
3.从日期中提取时间的各部分
db2 ： select hour(current_timestamp) hr, minute(current_timestamp) min,
second(current_timestamp) sec, day(current_timestamp) dy,
month(current_timestamp) mth, year(current_timestamp) yr from t1;
oracle: select to_number(to_char(sysdate,'hh24')) hour,
to_number(to_char(sysdate,'mi')) min,
to_number(to_char(sysdate,'ss')) sec,
to_number(to_char(sysdate,'dd')) day,
to_number(to_char(sysdate,'mm')) mth,
to_number(to_char(sysdate,'yyyy')) year from dual;
postgresql: select to_number(to_char(current_timestamp,'hh24'),'99') as hr,
to_number(to_char(current_timestamp,'mi'),'99') as min,
to_number(to_char(current_timestamp,'ss'),'99') as sec,
to_number(to_char(current_timestamp,'dd'),'99') as day,
to_number(to_char(current_timestamp,'mm'),'99') as mth,
to_number(to_char(current_timestamp,'yyyy'),'99') as yr
from t1;
mysql: select date_format(current_timestamp,'%k') hr,
date_format(current_timestamp,'%l') min,
date_format(current_timestamp,'%s') sec,
date_format(current_timestamp,'%d') dy,
date_format(current_timestamp,‘%m’) mon,
date_format(current_timestamp,'%Y') yr from t1;
sqlserver: select datepart(hour,getdate()) hr, depart(minute,getdate()) min,
datepart(second,getdate()) sec,depart(day,getdate()) dy,
datepart(month,getdate()) mon,depart(year,getdate()) yr from t1;
Note：这个操作是在where条件之前执行，要想改进效率必须先执行where条件，再执行日期的分解操作，用处还可以
4.确定某个月的第一天和最后一天
db2: select (current_date-day(current_date) day +1 day) firstday,
(current_date + 1 month - day(current_date) day) lastday from t1;
oracle: select tranc(sysdate,'mm') firstday,last_day(sysdate) lastday from dual;
postgres