1. 计算一年有多少天1.1. 方案1.1.1. 找到当前年份的第一天1.1.2. 加上1年以得到下一年的第一天1.1.3. 得到的结果减去第一步得到的结果1.2. DB21.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. Oracle1.3.1. sql

select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')   from dual

1.4. PostgreSQL1.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. MySQL1.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 Server1.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. DB22.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. Oracle2.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. PostgreSQL2.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. MySQL2.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 Server2.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. DB23.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. Oracle3.2.1. sql

select trunc(sysdate,'mm') firstday,        last_day(sysdate) lastday   from dual

3.3. PostgreSQL3.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. MySQL3.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 Server3.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. DB24.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. Oracle4.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. PostgreSQL4.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. MySQL4.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 Server4.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. DB25.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. Oracle5.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. PostgreSQL5.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. MySQL5.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 Server5.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. DB26.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. Oracle6.2.1. sql

select to_char(          last_day(add_months(trunc(sysdate,'y'),1)),         'DD')   from t1

6.3. PostgreSQL6.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. MySQL6.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 Server6.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