獲取指定日期相關DATENAME和DATEPART數據

DATENAME和DATEPART有何區別，Insus.NET寫成一個函數，可以方便查詢與對比：

一個是返回一個字元串，另一個是返回一個整數。

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

傳入一個日期，試試運行結果：