ORACLE TRUNC()函数

TRUNC()：类似截取函数，按指定的格式截取输入的数据。

1.【trunc(for dates)】TRUNC()函数处理日期

    语法格式：TRUNC（date[,fmt]）

　 其中：date 一个日期值；fmt 日期格式。

    该日期将按指定的日期格式截取；忽略它则由最近的日期截取。

   示例：

   select trunc(sysdate) from dual;--2017/2/13,返回当前时间
   select trunc(sysdate,'yy') from dual;--2017/1/1,返回当年第一天
   select trunc(sysdate,'mm') from dual;--2017/2/1,返回当月的第一天
   select trunc(sysdate,'d') from dual;--2017/2/12,返回当前星期的第一天，即星期天
   select trunc(sysdate,'dd') from dual;--2017/2/13,返回当前日期,今天是2017/2/13
   select trunc(sysdate ,'HH24') from dual;--2017/2/13 15:00:00,返回本小时的开始时间
   select trunc(sysdate ,'MI') from dual;--2017/2/13 15:13:00,返回本分钟的开始时间

2.【TRUNC(for number)】TRUNC()函数处理number型数字

    语法格式：TRUNC（number[,decimals]）

    其中： number 待做截取处理的数值；decimals 指明需保留小数点后面的位数，可选项，忽略它则截去所有的小数部分。

    注意：截取时并不对数据进行四舍五入。

    示例：

    select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数后面的截去;
    select trunc(123.567,-2) from dual;--100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
    select trunc(123.567) from dual;--123,默认截去小数点后面的部分;

 

注意：

例子

select x1.*,case when (trunc(  ( to_char(sysdate, 'yyyy.MM') -x1.borthday )) between 0 and 25) then '0-25'   
when (trunc(  ( to_char(sysdate, 'yyyy.MM') -x1.borthday )) between 26 and 30) then '26-30'
when (trunc(  ( to_char(sysdate, 'yyyy.MM') -x1.borthday )) between 31 and 35) then '31-35'  
when (trunc(  ( to_char(sysdate, 'yyyy.MM') -x1.borthday )) between 36 and 40) then '36-40'
when (trunc(  ( to_char(sysdate, 'yyyy.MM') -x1.borthday )) between 41 and 45) then '41-45'  
when (trunc(  ( to_char(sysdate, 'yyyy.MM') -x1.borthday )) between 46 and 50) then '46-50'   
when (trunc(  ( to_char(sysdate, 'yyyy.MM') -x1.borthday )) between 51 and 55) then '51-55'    
when (trunc(  ( to_char(sysdate, 'yyyy.MM') -x1.borthday )) between 56 and 60) then '56-60'    
when (trunc(  ( to_char(sysdate, 'yyyy.MM') -x1.borthday )) between 61 and 10000) then '60以上'     else null end  age_fb
  from (select t1.code,
               t1.name_value,
               t1.sex,
               t1.company,
               t2.prodline_name,
               t1.group_value,
               t1.sun_group,
               t1.class_group,
               t1.post_code,
               t1.post_name,
               t1.post_type,
               t1.person_type,
               t1.work_year,
               t1.rank,
               t1.grade,
               t1.borthday,
               t1.nation,
               t1.native_place,
               t1.education,
               t1.learn_type,
               t1.id_number,
               t1.political_status,
               t1.zzzgdj_level,
               t1.wg_status,
               t1.zw_level,
               t1.zw_name,
               t1.pk_month,
               t2.department,
               t2.category,
               '在职' as type_value,
                case when t1.wg_status in ('在职在岗','见习','专项工作') then '在岗' else '不在岗' end zg_status
          from FACT_ZZRYHMC t1
          left join FACT_EMPLOYMENT_SUPPLEMENT t2 on t1.code =
                                                     t2.person_number
                                                 and t1.pk_month =
                                                    t2.pk_month
                                                    where t1.pk_month=(select CVALUE from sjck.dim_zb where zb = 'ETL_TIME')
 
        )X1