获取指定日期相关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

 

传入一个日期，试试运行结果：