选读SQL经典实例笔记06_日期处理(上)
1.选读SQL经典实例笔记23_读后总结与感想兼导读2.选读SQL经典实例笔记01_检索和排序3.选读SQL经典实例笔记02_多表查询4.选读SQL经典实例笔记03_DML和元数据5.选读SQL经典实例笔记04_日期运算(上)6.选读SQL经典实例笔记05_日期运算(下)
7.选读SQL经典实例笔记06_日期处理(上)
8.选读SQL经典实例笔记07_日期处理(下)9.选读SQL经典实例笔记08_区间查询10.选读SQL经典实例笔记09_数值处理11.选读SQL经典实例笔记10_高级查询12.选读SQL经典实例笔记11_结果集变换13.选读SQL经典实例笔记12_桶、图和小计14.选读SQL经典实例笔记13_case与聚合15.选读SQL经典实例笔记14_层次查询16.选读SQL经典实例笔记15_窗口函数17.选读SQL经典实例笔记16_逻辑否定18.选读SQL经典实例笔记17_最多和最少19.选读SQL经典实例笔记18_Exactly20.选读SQL经典实例笔记19_Any和All21.选读SQL经典实例笔记20_Oracle语法示例22.选读SQL经典实例笔记21_字符串处理23.选读SQL经典实例笔记22_2版增补1. 计算一年有多少天
1.1. 方案
1.1.1. 找到当前年份的第一天
1.1.2. 加上1年以得到下一年的第一天
1.1.3. 得到的结果减去第一步得到的结果
1.2. DB2数据库
1.2.1. sql语句
select days((curr_year + 1 year)) - days(curr_year)
from (
select (current_date -
dayofyear(current_date) day +
1 day) curr_year
from t1
) x
1.3. Oracle数据库
1.3.1. sql语句
select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
from dual
1.4. PostgreSQL数据库
1.4.1. sql语句
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
1.5. MySQL数据库
1.5.1. sql语句
select datediff((curr_year + interval 1 year),curr_year)
from (
select adddate(current_date,-dayofyear(current_date)+1) curr_year
from t1
) x
1.6. SQL Server数据库
1.6.1. sql语句
select datediff(d,curr_year,dateadd(yy,1,curr_year))
from (
select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year
from t1
) x
2. 日期值里提取年月日时分秒
2.1. DB2数据库
2.1.1. sql语句
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
2.2. Oracle数据库
2.2.1. sql语句
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
2.3. PostgreSQL数据库
2.3.1. sql语句
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'),'9999') as yr
from t1
2.4. MySQL数据库
2.4.1. sql语句
select date_format(current_timestamp,'%k') hr,
date_format(current_timestamp,'%i') 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
2.5. SQL Server数据库
2.5.1. sql语句
select datepart( hour, getdate()) hr,
datepart( minute,getdate()) min,
datepart( second,getdate()) sec,
datepart( day, getdate()) dy,
datepart( month, getdate()) mon,
datepart( year, getdate()) yr
from t1
3. 一个月的第一天和最后一天
3.1. DB2数据库
3.1.1. sql语句
select (current_date - day(current_date) day +1 day) firstday,
(current_date +1 month -day(current_date) day) lastday
from t1
3.2. Oracle数据库
3.2.1. sql语句
select trunc(sysdate,'mm') firstday,
last_day(sysdate) lastday
from dual
3.3. PostgreSQL数据库
3.3.1. sql语句
select firstday,
cast(firstday + interval '1 month'
- interval '1 day' as date) as lastday
from (
select cast(date_trunc('month',current_date) as date) as firstday
from t1
) x
3.4. MySQL数据库
3.4.1. sql语句
select date_add(current_date,
interval -day(current_date)+1 day) firstday,
last_day(current_date) lastday
from t1
3.5. SQL Server数据库
3.5.1. sql语句
select dateadd(day,-day(getdate())+1,getdate()) firstday,
dateadd(day,
-day(getdate( )),
dateadd(month,1,getdate())) lastday
from t1
4. 当前月份的第一个和最后一个星期一
4.1. DB2数据库
4.1.1. sql语句
with x (dy,mth,is_monday)
as (
select dy,month(dy),
case when dayname(dy)='Monday'
then 1 else 0
end
from (
select (current_date-day(current_date) day +1 day) dy
from t1
) tmp1
union all
select (dy +1 day), mth,
case when dayname(dy +1 day)='Monday'
then 1 else 0
end
from x
where month(dy +1 day) = mth
)
select min(dy) first_monday, max(dy) last_monday
from x
where is_monday = 1
4.2. Oracle数据库
4.2.1. sql语句
select next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday,
next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday
from dual
4.3. PostgreSQL数据库
4.3.1. sql语句
select first_monday,
case to_char(first_monday+28,'mm')
when mth then first_monday+28
else first_monday+21
end as last_monday
from (
select case sign(cast(to_char(dy,'d') as integer)-2)
when 0
then dy
when -1
then dy+abs(cast(to_char(dy,'d') as integer)-2)
when 1
then (7-(cast(to_char(dy,'d') as integer)-2))+dy
end as first_monday,
mth
from (
select cast(date_trunc('month',current_date) as date) as dy,
to_char(current_date,'mm') as mth
from t1
) x
) y
4.4. MySQL数据库
4.4.1. sql语句
select first_monday,
case month(adddate(first_monday,28))
when mth then adddate(first_monday,28)
else adddate(first_monday,21)
end last_monday
from (
select case sign(dayofweek(dy)-2)
when 0 then dy
when -1 then adddate(dy,abs(dayofweek(dy)-2))
when 1 then adddate(dy,(7-(dayofweek(dy)-2)))
end first_monday,
mth
from (
select adddate(adddate(current_date,-day(current_date)),1) dy,
month(current_date) mth
from t1
) x
) y
4.5. SQL Server数据库
4.5.1. sql语句
with x (dy,mth,is_monday)
as (
select dy,mth,
case when datepart(dw,dy) = 2
then 1 else 0
end
from (
select dateadd(day,1,dateadd(day,-day(getdate()),getdate())) dy,
month(getdate()) mth
from t1
) tmp1
union all
select dateadd(day,1,dy),
mth,
case when datepart(dw,dateadd(day,1,dy)) = 2
then 1 else 0
end
from x
where month(dateadd(day,1,dy)) = mth
)
select min(dy) first_monday,
max(dy) last_monday
from x
where is_monday = 1
5. 一年中所有的星期五
5.1. DB2数据库
5.1.1. sql语句
with x (dy,yr)
as (
select dy, year(dy) yr
from (
select (current_date -
dayofyear(current_date) days +1 days) as dy
from t1
) tmp1
union all
select dy+1 days, yr
from x
where year(dy +1 day) = yr
)
select dy
from x
where dayname(dy) = 'Friday'
5.2. Oracle数据库
5.2.1. sql语句
with x
as (
select trunc(sysdate,'y')+level-1 dy
from t1
connect by level <=
add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
)
select *
from x
where to_char( dy, 'dy') = 'fri'
5.3. PostgreSQL数据库
5.3.1. sql语句
select cast(date_trunc('year',current_date) as date)
+ x.id as dy
from generate_series (
0,
( select cast(
cast(
date_trunc('year',current_date) as date)
+ interval '1 years' as date)
- cast(
date_trunc('year',current_date) as date) )-1
) x(id)
where to_char(
cast(
date_trunc('year',current_date)
as date)+x.id,'dy') = 'fri'
5.4. MySQL数据库
5.4.1. sql语句
select dy
from (
select adddate(x.dy,interval t500.id-1 day) dy
from (
select dy, year(dy) yr
from (
select adddate(
adddate(current_date,
interval -dayofyear(current_date) day),
interval 1 day ) dy
from t1
) tmp1
) x,
t500
where year(adddate(x.dy,interval t500.id-1 day)) = x.yr
) tmp2
where dayname(dy) = 'Friday'
5.5. SQL Server数据库
5.5.1. sql语句
with x (dy,yr)
as (
select dy, year(dy) yr
from (
select getdate()-datepart(dy,getdate())+1 dy
from t1
) tmp1
union all
select dateadd(dd,1,dy), yr
from x
where year(dateadd(dd,1,dy)) = yr
)
select x.dy
from x
where datename(dw,x.dy) = 'Friday'
option (maxrecursion 400)
6. 判断闰年
6.1. DB2数据库
6.1.1. sql语句
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
6.2. Oracle数据库
6.2.1. sql语句
select to_char(
last_day(add_months(trunc(sysdate,'y'),1)),
'DD')
from t1
6.3. PostgreSQL数据库
6.3.1. sql语句
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_trunc('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
6.4. MySQL数据库
6.4.1. sql语句
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
6.5. SQL Server数据库
6.5.1. sql语句
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
合集:
选读SQL经典实例
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业