SQL Server 2008中的创建格式化日期(1)
发布时间：2015-06-29 00:00 来源：未知
让我们来创建dbo.format_date 函数，如下所示。
/****** Object: UserDefinedFunction [dbo].[format_date] Script Date: 05/12/2009 23:19:35 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[format_date]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[format_date] GO /****** Object: UserDefinedFunction [dbo].[format_date] Script Date: 05/12/2009 23:19:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET CONCAT_NULL_YIELDS_NULL OFF go 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)<>0 begin set @formatteddate=replace(@formatteddate,'ucase','') set @formatteddate=upper(@formatteddate) end if charindex('lcase',@formatteddate)<>0 begin set @formatteddate=replace(@formatteddate,'lcase','') set @formatteddate=lower(@formatteddate) end return @formatteddate end GO
现在让我们使用不同的场景来看看这个函数的使用。下面关键字中的任何一个都可以联合使用来显示不同格式的日期。
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
结果：
Date
--------------
2009/01/12
场景 2
要以YYYY-MM-DD格式显示日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MM-DD') as Date
结果：
Date
-------------------
2009-01-12
场景 3
要显示年份和季节，那么执行下面的命令。
select dbo.format_date(GETDATE(),'Year: YYYY, Quarter: QQ ') as Date
结果：
Date
-----------------------
Year: 2009, Quarter: 1
场景 4
要以24小时制格式显示时间，执行下面的命令。
select dbo.format_date(GETDATE(),'24HH:MI:SS') as Time
结果：
Time
------------------------
23:10:07
场景 5
要以12小时制格式显示时间，执行下面的命令。
select dbo.format_date(GETDATE(),'12HH:MI:SS AMPM') as Time
结果：
Time
-----------------------
11:11:02 PM
场景 6
要以日期形式为YYYY/MM/DD 和时间形式为24小时制来显示，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY/MM/DD 24HH:MI:SS') as DateTime
结果：
DateTime
-----------------------
2009/01/12 23:11:44
场景 7
要以日期形式为YYYY/MM/DD和时间形式为12小时制来显示，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY/MM/DD 12HH:MI:SS AMPM') as DateTime
结果：
DateTime
-------------------------
2009/01/12 11:13:27 PM
场景 8
要以DD-MM-YY 的形式来显示日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'DD-MM-YR') as Date
结果：
Date
------------------------
12-01-09
场景 9
要以DDMMYY的形式显示日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'DDMMYR') as Date
结果：
Date
----------------------
120109
场景 10
要显示日期和时间以你可以使用它来作为文件名称后缀的形式，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY_MM_DD_HH_MI_SS_MS_MCS_NS') as FileNameSuffix
结果：
FileNameSuffix
--------------------------------------------
2009_01_12_HH_16_18_200_200000_200000000
场景 11
要显示日期、时间、月份名称和日子的名称，那么执行下面的命令。
select dbo.format_date(GETDATE(),'DAY, MONTH DD, YYYY 12HH:MI:SS AMPM') as DateTime
结果：
DateTime
---------------------------------------
Monday, January 12, 2009 11:20:31 PM
场景 12
要以时间偏移量来显示日期、时间、月份名称和日子的名称，那么执行下面的命令。
select dbo.format_date(GETDATE(),'DAY MONTH DDth, YR 12HH:MI:SS TZ') as DateTime
结果：
DateTime
------------------------------------------
Monday January 12th, 09 11:21:42 +00:00
场景 13
要显示unix posix时间，那么执行下面的命令。
select dbo.format_date(GETDATE(),'Your Unix time is: UNIXPOSIX') as POSIX
结果：
POSIX
----------------------------------
Your Unix time is: 1231802580
场景 14
要显示年份和星期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'Year: YYYY, Week: WW') as YearWeek
结果：
YearWeek
-------------------------
Year: 2009, Week: 03
场景15
要显示带有月份名称的日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MONTH-DD') as Date
结果：
Date
--------------------------
2009-January-12
场景16
要显示带有短的月份名称的日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MON-DD') as Date
结果：
Date
------------------------
2009-Jan-12
场景17
要显示带有大写的短的月份名称的日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MON-DD ucase') as Date
结果：
Date
----------------------
2009-JAN-12
场景18
要显示带有小写的月份名称的日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MONTH-DD lcase') as Date
结果：
Date
-------------------
2009-january-12
场景19
如果你不传递参数，那么这个函数使用默认的格式化日期和时间的方法。执行下面的命令：
select dbo.format_date(NULL,NULL)
select dbo.format_date(NULL,'YYYY')
select dbo.format_date(getdate(),NULL)
结果：
Date
-----------------------
2009-01-12 11:29:17 PM
Year
-----------------------
2009
Date
-----------------------
2009-01-12 11:29:17 PM
该文介绍了怎样创建一个命令函数来获得不同国家和应用程序所要求的大多数日期格式。
让我们来创建dbo.format_date 函数，如下所示。
/****** Object: UserDefinedFunction [dbo].[format_date] Script Date: 05/12/2009 23:19:35 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[format_date]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[format_date] GO /****** Object: UserDefinedFunction [dbo].[format_date] Script Date: 05/12/2009 23:19:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET CONCAT_NULL_YIELDS_NULL OFF go 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)<>0 begin set @formatteddate=replace(@formatteddate,'ucase','') set @formatteddate=upper(@formatteddate) end if charindex('lcase',@formatteddate)<>0 begin set @formatteddate=replace(@formatteddate,'lcase','') set @formatteddate=lower(@formatteddate) end return @formatteddate end GO
现在让我们使用不同的场景来看看这个函数的使用。下面关键字中的任何一个都可以联合使用来显示不同格式的日期。
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
结果：
Date
--------------
2009/01/12
场景 2
要以YYYY-MM-DD格式显示日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MM-DD') as Date
结果：
Date
-------------------
2009-01-12
场景 3
要显示年份和季节，那么执行下面的命令。
select dbo.format_date(GETDATE(),'Year: YYYY, Quarter: QQ ') as Date
结果：
Date
-----------------------
Year: 2009, Quarter: 1
场景 4
要以24小时制格式显示时间，执行下面的命令。
select dbo.format_date(GETDATE(),'24HH:MI:SS') as Time
结果：
Time
------------------------
23:10:07
场景 5
要以12小时制格式显示时间，执行下面的命令。
select dbo.format_date(GETDATE(),'12HH:MI:SS AMPM') as Time
结果：
Time
-----------------------
11:11:02 PM
场景 6
要以日期形式为YYYY/MM/DD 和时间形式为24小时制来显示，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY/MM/DD 24HH:MI:SS') as DateTime
结果：
DateTime
-----------------------
2009/01/12 23:11:44
场景 7
要以日期形式为YYYY/MM/DD和时间形式为12小时制来显示，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY/MM/DD 12HH:MI:SS AMPM') as DateTime
结果：
DateTime
-------------------------
2009/01/12 11:13:27 PM
场景 8
要以DD-MM-YY 的形式来显示日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'DD-MM-YR') as Date
结果：
Date
------------------------
12-01-09
场景 9
要以DDMMYY的形式显示日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'DDMMYR') as Date
结果：
Date
----------------------
120109
场景 10
要显示日期和时间以你可以使用它来作为文件名称后缀的形式，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY_MM_DD_HH_MI_SS_MS_MCS_NS') as FileNameSuffix
结果：
FileNameSuffix
--------------------------------------------
2009_01_12_HH_16_18_200_200000_200000000
场景 11
要显示日期、时间、月份名称和日子的名称，那么执行下面的命令。
select dbo.format_date(GETDATE(),'DAY, MONTH DD, YYYY 12HH:MI:SS AMPM') as DateTime
结果：
DateTime
---------------------------------------
Monday, January 12, 2009 11:20:31 PM
场景 12
要以时间偏移量来显示日期、时间、月份名称和日子的名称，那么执行下面的命令。
select dbo.format_date(GETDATE(),'DAY MONTH DDth, YR 12HH:MI:SS TZ') as DateTime
结果：
DateTime
------------------------------------------
Monday January 12th, 09 11:21:42 +00:00
场景 13
要显示unix posix时间，那么执行下面的命令。
select dbo.format_date(GETDATE(),'Your Unix time is: UNIXPOSIX') as POSIX
结果：
POSIX
----------------------------------
Your Unix time is: 1231802580
场景 14
要显示年份和星期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'Year: YYYY, Week: WW') as YearWeek
结果：
YearWeek
-------------------------
Year: 2009, Week: 03
场景15
要显示带有月份名称的日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MONTH-DD') as Date
结果：
Date
--------------------------
2009-January-12
场景16
要显示带有短的月份名称的日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MON-DD') as Date
结果：
Date
------------------------
2009-Jan-12
场景17
要显示带有大写的短的月份名称的日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MON-DD ucase') as Date
结果：
Date
----------------------
2009-JAN-12
场景18
要显示带有小写的月份名称的日期，那么执行下面的命令。
select dbo.format_date(GETDATE(),'YYYY-MONTH-DD lcase') as Date
结果：
Date
-------------------
2009-january-12
场景19
如果你不传递参数，那么这个函数使用默认的格式化日期和时间的方法。执行下面的命令：
select dbo.format_date(NULL,NULL)
select dbo.format_date(NULL,'YYYY')
select dbo.format_date(getdate(),NULL)
结果：
Date
-----------------------
2009-01-12 11:29:17 PM
Year
-----------------------
2009
Date
-----------------------
2009-01-12 11:29:17 PM
该文介绍了怎样创建一个命令函数来获得不同国家和应用程序所要求的大多数日期格式。
