绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
怎样创建一个命令函数来获得不同国家和应用程序所要求的大多数日期格式
2023-03-20 16:04:07

今天领导让我导出17年至19年的XX消费记录。还要按月汇总...我也醉了。查了各种资料都是说用convert(),再用 left截断。后来终于在生产库里边找到了format_date()函数。真不错,向无名英雄致敬!

这篇文章介绍了怎样创建一个命令函数来获得不同国家和应用程序所要求的大多数日期格式。

源码如下

Create function [dbo].[format_date]  
 
(@inputdate datetime ,@format varchar(500))  
 
returns varchar(500)  
 
as 
 
begin 
 
declare @year varchar(4) --YYYY  
 
declare @shortyear varchar(4) --Yr  
 
declare @quarter varchar(4) --QQ  
 
declare @month varchar(2) --MM  
 
declare @week varchar(2) --WW  
 
declare @day varchar(2) --DD  
 
declare @24hours varchar(2) --24HH  
 
declare @12hours varchar(2) --HH  
 
declare @minutes varchar(2) --MI  
 
declare @seconds varchar(2) --SS  
 
declare @milliseconds varchar(3) --MS  
 
declare @microseconds varchar(6) --MCS  
 
declare @nanoseconds varchar(9) --NS  
 
declare @dayname varchar(15) --DAY  
 
declare @monthname varchar(15) --MONTH  
 
declare @shortmonthname varchar(15) --MON  
 
declare @AMPM varchar(15) --AMPM  
 
declare @TZ varchar(15) --TZ  
 
declare @UNIXPOSIX varchar(15) --UNIXPOSIX  
 
--UCASE  
 
--LCASE  
 
declare @formatteddate varchar(500)  
 
--Assign current date and time to  
 
if (@inputdate is NULL or @inputdate ='')  
 
begin 
 
set @inputdate = getdate()  
 
end 
 
if (@format is NULL or @format ='')  
 
begin 
 
set @format ='YYYY-MM-DD 12HH:MI:SS AMPM' 
 
end 

--set all values   
set @year = convert(varchar(4),year(@inputdate))  
 
set @shortyear = right(@year,2)  
 
set @quarter = convert(varchar(1),datepart(QQ,(@inputdate)))  
 
set @month = right('0'+convert(varchar(2),month(@inputdate)),2)  
 
set @week = right('0'+convert(varchar(2),datepart(ww,(@inputdate))),2)  
 
set @day = right('0'+convert(varchar(2),day(@inputdate)),2)  
 
set @24hours = right('0'+convert(varchar(2),datepart(hh,@inputdate)),2)  
 
set @TZ = convert(varchar(10),datename(TZ,convert(varchar(20),@inputdate)))  
 
set @UNIXPOSIX = convert(varchar(15),datediff(ss,convert(datetime,'01/01/1970 00:00:000'),@inputdate))  
 
if datepart(hh,@inputdate) >12  
 
begin 
 
set @12hours = right('0'+convert(varchar(2),datepart(hh,@inputdate)) -12,2)  
 
end 
 
else 
 
begin 
 
set @12hours = right('0'+convert(varchar(2),datepart(hh,@inputdate)) ,2)  
 
end 
 
if datepart(hh,@inputdate) >11  
 
begin 
 
set @AMPM ='PM' 
 
end 
 
else 
 
begin 
 
set @AMPM ='AM' 
 
end 
 
set @minutes = right('0'+convert(varchar(2),datepart(n,@inputdate)),2)  
 
set @seconds = right('0'+convert(varchar(2),datepart(ss,@inputdate)),2)  
 
set @milliseconds = convert(varchar(3),datepart(ms,@inputdate))  
 
set @microseconds = convert(varchar(6),datepart(mcs,@inputdate))  
 
set @nanoseconds = convert(varchar(9),datepart(ns,@inputdate))  
 
set @dayname = datename(weekday,@inputdate)  
 
set @monthname = datename(mm,@inputdate)  
 
set @shortmonthname= left(datename(mm,@inputdate),3)  
 
set @formatteddate = @format  
 
set @formatteddate=replace(@formatteddate,'MONTH',@monthname)  
 
set @formatteddate=replace(@formatteddate,'MON',@shortmonthname)  
 
set @formatteddate=replace(@formatteddate,'AMPM',@AMPM)  
 
set @formatteddate=replace(@formatteddate,'YYYY',@year)  
 
set @formatteddate=replace(@formatteddate,'Yr',@shortyear)  
 
set @formatteddate=replace(@formatteddate,'QQ',@quarter)  
 
set @formatteddate=replace(@formatteddate,'WW',@week)  
 
set @formatteddate=replace(@formatteddate,'MM',@month)  
 
set @formatteddate=replace(@formatteddate,'DD',@Day)  
 
set @formatteddate=replace(@formatteddate,'24HH',@24hours)  
 
set @formatteddate=replace(@formatteddate,'12HH',@12hours)  
 
set @formatteddate=replace(@formatteddate,'Mi',@minutes)  
 
set @formatteddate=replace(@formatteddate,'SS',@seconds)  
 
set @formatteddate=replace(@formatteddate,'MS',@milliseconds)  
 
set @formatteddate=replace(@formatteddate,'MCS',@microseconds)  
 
set @formatteddate=replace(@formatteddate,'NS',@nanoseconds)  
 
set @formatteddate=replace(@formatteddate,'DAY',@dayname)  
 
set @formatteddate=replace(@formatteddate,'TZ',@TZ)  
 
set @formatteddate=replace(@formatteddate,'UNIXPOSIX',@UNIXPOSIX)  
 
if charindex('ucase',@formatteddate)<>  
 
begin 
 
set @formatteddate=replace(@formatteddate,'ucase','')  
 
set @formatteddate=upper(@formatteddate)  
 
end 
 
if charindex('lcase',@formatteddate)<>   
 
begin 
 
set @formatteddate=replace(@formatteddate,'lcase','')  
 
set @formatteddate=lower(@formatteddate)  
 
end 
 
return @formatteddate  
 
end 
 


现在让我们使用不同的场景来看看这个函数的使用。下面关键字中的任何一个都可以联合使用来显示不同格式的日期。

YYYY - YYYY格式的年份,包括百年

Yr - YY格式的年份

QQ - 显示季度

MM - 显示月份

WW - 显示星期

DD - 显示日子

24HH - 以24小时制的形式显示小时

12HH - 以12小时制的形式显示小时

MI - 显示分钟

SS - 显示秒

MS - 显示毫秒

MCS - 显示微秒

NS - 显示十亿分之一秒

DAY - 显示日子的名称,例如:Monday

MONTH- - 显示月份的名称,例如:August

MON - 显示短的月份名称,例如:Aug

AMPM - 显示AM/PM 用于12小时制格式

TZ - 显示时间偏移

UNIXPOSIX - 显示unix posix时间。秒数从1/1/1970开始

UCASE - 以大写显示结果

LCASE - 以小写显示结果

/*场景 1
如果你想以YYYY/MM/DD的形式显示日期,那么执行下面的命令。
/*
select dbo.format_date(GETDATE(),'YYYY/MM/DD') as Date

--结果 :2019/08/10

/*场景 2
要以YYYY-MM-DD格式显示日期,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'YYYY-MM-DD') as Date

--结果 :2019-08-10

/*场景 3
要显示年份和季节,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'Year: YYYY, Quarter: QQ ') as Date

--结果 :Year: 2019, Quarter: 3 

/*场景 4
要以24小时制格式显示时间,执行下面的命令。
*/
select dbo.format_date(GETDATE(),'24HH:MI:SS') as Time

--结果 :14:18:36

/*场景 5
要以12小时制格式显示时间,执行下面的命令。
*/
select dbo.format_date(GETDATE(),'12HH:MI:SS AMPM') as Time

--结果 :2:19:57 PM

/*场景 6
要以日期形式为YYYY/MM/DD 和时间形式为24小时制来显示,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'YYYY/MM/DD 24HH:MI:SS') as DateTime

--结果 :2019/08/10 14:20:38

/*场景 7
要以日期形式为YYYY/MM/DD和时间形式为12小时制来显示,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'YYYY/MM/DD 12HH:MI:SS AMPM') as DateTime

--结果 :2019/08/10 2:21:41 PM

/*场景 8
要以DD-MM-YY 的形式来显示日期,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'DD-MM-YR') as Date
--结果 :10-08-19

/*场景 9

要以DDMMYY的形式显示日期,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'DDMMYR') as Date
--结果 :100819

/*
场景 10

要显示日期和时间以你可以使用它来作为文件名称后缀的形式,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'YYYY_MM_DD_HH_MI_SS_MS_MCS_NS') as FileNameSuffix
--结果 :2019_08_10_HH_24_30_773_773000_773000000

/*场景 11

要显示日期、时间、月份名称和日子的名称,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'DAY, MONTH DD, YYYY 12HH:MI:SS AMPM') as DateTime
--结果 :星期六, 08 10, 2019 2:25:23 PM

/*场景 12

要以时间偏移量来显示日期、时间、月份名称和日子的名称,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'DAY MONTH DDth, YR 12HH:MI:SS TZ') as DateTime
--结果 :星期六 08 10th, 19 2:26:04 +00:00

/*场景 13

要显示unix posix时间,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'Your Unix time is: UNIXPOSIX') as POSIX
--结果 :Your Unix time is: 1565447200

/*场景 14

要显示年份和星期,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'Year: YYYY, Week: WW') as YearWeek
--结果 :Year: 2019, Week: 32

/*场景15

要显示带有月份名称的日期,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'YYYY-MONTH-DD') as Date
--结果 :2019-08-10

/*场景16

要显示带有短的月份名称的日期,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'YYYY-MON-DD') as Date
--结果 :2019-08-10

/*
场景17

要显示带有大写的短的月份名称的日期,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'YYYY-MON-DD ucase') as Date
--结果 :2019-08-10 

/*场景18

要显示带有小写的月份名称的日期,那么执行下面的命令。
*/
select dbo.format_date(GETDATE(),'YYYY-MONTH-DD lcase') as Date
--结果 :2019-08-10 

/*场景19

如果你不传递参数,那么这个函数使用默认的格式化日期和时间的方法。执行下面的命令:
*/
select dbo.format_date(NULL,NULL)

select dbo.format_date(NULL,'YYYY')

select dbo.format_date(getdate(),NULL)

--2019-08-10 2:36:56 PM
--2019
--2019-08-10 2:36:56 PM


分享好友

分享这个小栈给你的朋友们,一起进步吧。

SQLServer
创建时间:2023-03-20 14:06:14
美国Microsoft公司推出的一种关系型数据库系统。SQL Server是一个可扩展的、高性能的、为分布式客户机/服务器计算所设计的数据库管理系统,实现了与WindowsNT的有机结合,提供了基于事务的企业级信息管理系统方案。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 飘絮絮絮丶
    专家
戳我,来吐槽~