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
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