• 【TSQL】获取指定日期的常用前后节点(月初月末周一周末等等)


    注:周节点是根据中国习惯,视周一为起,周日为末。 

    /*---------------------------------
    函数:获取某日期的特定起止日期v0.02
    Author:AhDung
    Update:201607251729。无论@@datefirst为何值,均不影响周节点
    ---------------------------------*/
    ALTER FUNCTION dbo.FGetSpecialDate_ahdung(@date DATE, @SpcDate VARCHAR(20))
    RETURNS DATE AS
    BEGIN
    IF @date IS    NULL RETURN NULL
    IF @SpcDate IN ('YearBeg','YB','NC','年初') RETURN DATENAME(YEAR,@date)+'-01-01'
    ELSE IF @SpcDate IN ('YearEnd','YE','NW','年尾','年末') RETURN DATENAME(YEAR,@date)+'-12-31'
    ELSE IF @SpcDate IN ('QuarterBeg','QtBeg','QB','JC','季初') RETURN DATENAME(YEAR,@date)+CASE WHEN MONTH(@date)<=3 THEN '-01' WHEN MONTH(@date)<=6 THEN '-04' WHEN MONTH(@date)<=9 THEN '-07' ELSE '-10' END +'-01'
    ELSE IF @SpcDate IN ('QuarterEnd','QtEnd','QE','JW','季尾','季末') RETURN DATENAME(YEAR,@date)+CASE WHEN MONTH(@date)<=3 THEN '-03-31' WHEN MONTH(@date)<=6 THEN '-06-30' WHEN MONTH(@date)<=9 THEN '-09-30' ELSE '-12-31' END
    ELSE IF @SpcDate IN ('MonBeg','MB','YC','月初') RETURN CONVERT(CHAR(7),@date,120)+'-01'
    ELSE IF @SpcDate IN ('MonEnd','ME','YW','月尾','月末') RETURN DATEADD(DAY,-1,CONVERT(CHAR(7),DATEADD(MONTH,1,@date),120)+'-01')
    ELSE IF @SpcDate IN ('WeekBeg','WB','ZY','周一','WeekEnd','WE','ZR','ZM','周日','周末')
    BEGIN
    DECLARE @dw INT=DATEPART(dw,@date)
    DECLARE @diff INT = 9-@@DATEFIRST
    DECLARE @d INT= CASE WHEN @dw<@diff THEN @dw+7 ELSE @dw END
    IF @SpcDate IN ('WeekBeg','WB','ZY','周一') RETURN DATEADD(DAY,@diff-@d,@date)
    RETURN DATEADD(DAY,@diff-@d+6,@date)
    END
    RETURN @date
    END
  • 相关阅读:
    @Value注解无法为static 变量赋值
    CloseableHttpClient方式配置代理服务器访问外网
    微信小程序上传图片
    centos7 无界面静默安装 oracle
    Linux安装配置JDK
    java导出Excel定义导出模板
    ECharts柱状图
    Java获取客户端真实IP
    Eclipse控制台输出log日志中文乱码
    $_SRRVER常用用法
  • 原文地址:https://www.cnblogs.com/ahdung/p/3081387.html
Copyright © 2020-2023  润新知