获取指定日期相关DATENAME和DATEPART数据

佚名 6年前 (2019-05-10) 随笔 846人围观抢沙发百度已收录

DATENAME和DATEPART有何区别，Insus.NET写成一个函数，可以方便查询与对比：

一个是返回一个字符串，另一个是返回一个整数。

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-10
-- Update date: 2019-05-10
-- Description: 获取指定日期相关DATENAME和DATEPART数据
-- =============================================

CREATE FUNCTION [dbo].[tvf_DateDatas]
(
    @SpecifiedDate DATETIME2
)
RETURNS @dumpWeekTable TABLE
(
    [Datepart] NVARCHAR(MAX) NOT NULL,
    [DATENAME_ReturnValue] NVARCHAR(MAX) NULL,
    [DATEPART_ReturnValue] NVARCHAR(MAX) NULL
)
AS
BEGIN    
    INSERT INTO @dumpWeekTable ([Datepart],[DATENAME_ReturnValue],[DATEPART_ReturnValue]) VALUES 
    ('year, yyyy, yy',DATENAME(yy,@SpecifiedDate),DATEPART(yy,@SpecifiedDate)),
    ('quarter, qq, q',DATENAME(q,@SpecifiedDate),DATEPART(q,@SpecifiedDate)),
    ('month, mm, m',DATENAME(m,@SpecifiedDate),DATEPART(m,@SpecifiedDate)),
    ('dayofyear, dy, y',DATENAME(y,@SpecifiedDate),DATEPART(y,@SpecifiedDate)),
    ('day, dd, d',DATENAME(d,@SpecifiedDate),DATEPART(d,@SpecifiedDate)),
    ('week, wk, ww',DATENAME(ww,@SpecifiedDate),DATEPART(ww,@SpecifiedDate)),
    ('weekday, dw',DATENAME(dw,@SpecifiedDate),DATEPART(dw,@SpecifiedDate)),
    ('hour, hh',DATENAME(hh,@SpecifiedDate),DATEPART(hh,@SpecifiedDate)),
    ('minute, n',DATENAME(n,@SpecifiedDate),DATEPART(n,@SpecifiedDate)),
    ('second, ss, s',DATENAME(s,@SpecifiedDate),DATEPART(s,@SpecifiedDate)),
    ('millisecond, ms',DATENAME(ms,@SpecifiedDate),DATEPART(ms,@SpecifiedDate)),
    ('microsecond, mcs',DATENAME(mcs,@SpecifiedDate),DATEPART(mcs,@SpecifiedDate)),
    ('nanosecond, ns',DATENAME(ns,@SpecifiedDate),DATEPART(ns,@SpecifiedDate)),
    ('TZoffset, tz',DATENAME(tz,@SpecifiedDate),DATEPART(tz,@SpecifiedDate)),
    ('ISO_WEEK, ISOWK, ISOWW',DATENAME(ISOWW,@SpecifiedDate),DATEPART(ISOWW,@SpecifiedDate))
    RETURN
END

GO