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