文章來源地址http://www.zghlxwxcb.cn/news/detail-553113.html

1.?計(jì)算一年有多少天

1.1.?方案

1.1.1.?找到當(dāng)前年份的第一天

1.1.2.?加上1年以得到下一年的第一天

1.1.3.?得到的結(jié)果減去第一步得到的結(jié)果

1.2.?DB2數(shù)據(jù)庫

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數(shù)據(jù)庫

1.3.1.??sql語句

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

1.4.?PostgreSQL數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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.?日期值里提取年月日時(shí)分秒

2.1.?DB2數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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.?一個(gè)月的第一天和最后一天

3.1.?DB2數(shù)據(jù)庫

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數(shù)據(jù)庫

3.2.1.??sql語句

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

3.3.?PostgreSQL數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

3.5.1.??sql語句

select dateadd(day,-day(getdate())+1,getdate()) firstday,
       dateadd(day,
               -day(getdate( )),
               dateadd(month,1,getdate())) lastday
   from t1

4.?當(dāng)前月份的第一個(gè)和最后一個(gè)星期一

4.1.?DB2數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

6.2.1.??sql語句

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

6.3.?PostgreSQL數(shù)據(jù)庫

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數(shù)據(jù)庫

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數(shù)據(jù)庫

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

到了這里，關(guān)于選讀SQL經(jīng)典實(shí)例筆記06_日期處理（上）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！