让我们来创建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 |
转载于:https://blog.51cto.com/2771253/524939