CREATE FUNCTION [dbo].[itvfFormatDateWithMask] ( @date AS DATETIME ,@format_string VARCHAR(50) )RETURNS TABLE ASRETURN ( /* SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'YYYY-MM-DD HH:mm:ss.fff') SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMMM Dx, YYYY hh:mmtt') SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMM Dx, YYYY') 'YYYY' - full year with century 'YY' - year without century 'Y' - last digit of year 'MMMM' - month name 'MMM' - month name first 3 chars only 'MM' - month number with leading zero 'M' - month number without leading zero) 'DDDD' - day name 'DD' - day number with leading zero 'D' - day number without leading zero 'HH' - hour with leading zero (24 hr format) 'H' - hour without leading zero 'hh' - hour with leading zero (12 hr format) 'h' - hour without leading zero (12 hr format) 'mm' - minutes with leading zero 'm' - minutes without leading zero 'ss' - seconds with leading zero 's' - seconds without leading zero 'fff' - milliseconds with leading zeros 'f' - milliseconds without leading zeros 'tt' - AM or PM 't' - A or P 'x' - st, nd, or th */ WITH fourRows(N) AS ( SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) ,cteTally(N) AS ( SELECT TOP (50) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM fourRows AS A CROSS JOIN fourRows AS B CROSS JOIN fourRows AS C ORDER BY 1 ) ,tokenizedString AS ( SELECT N ,REPLACE(C,SUBSTRING(CAST(C AS NVARCHAR(50)),CHARINDEX('""',CAST(C AS NVARCHAR(50)),1),CHARINDEX('""',CAST(C AS NVARCHAR(50)),2)),'ZZZZZZZ') AS C ,groupId = DENSE_RANK() OVER (ORDER BY C, _groupId) FROM ( SELECT N ,SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) AS C ,_groupId = N - ROW_NUMBER() OVER (PARTITION BY SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) ORDER BY N) FROM cteTally WHERE N <= LEN(@format_string) ) AS fs) SELECT (SELECT CASE REPLICATE(MIN(C),COUNT(*)) WHEN 'YYYY' THEN RIGHT('0000' + CAST(YEAR(@date) AS NVARCHAR(4)),4) WHEN 'YY' THEN RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),2) WHEN 'Y' THEN CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),1) AS INT) AS NVARCHAR(2)) WHEN 'MMMM' THEN DATENAME(month,@date) WHEN 'MMM' THEN LEFT(DATENAME(month,@date),3) WHEN 'MM' THEN RIGHT('00' + CAST(MONTH(@date) AS NVARCHAR(2)),2) WHEN 'M' THEN CAST(MONTH(@date) AS NVARCHAR(2)) WHEN 'DDDD' THEN DATENAME(weekday,@date) WHEN 'DD' THEN RIGHT('00' + CAST(DAY(@date) AS NVARCHAR(2)),2) WHEN 'D' THEN CAST(DAY(@date) AS NVARCHAR(2)) WHEN 'HH' THEN RIGHT('00' + CAST(DATEPART(hour,@date) AS NVARCHAR(2)),2) WHEN 'H' THEN CAST(DATEPART(hour,@date) AS NVARCHAR(2)) WHEN 'hh' THEN RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),2) WHEN 'h' THEN CASE WHEN LEFT( RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),2),1) = 0 THEN RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),1) ELSE RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),2) END WHEN 'mm' THEN RIGHT('00' + CAST(DATEPART(minute,@date) AS NVARCHAR(2)),2) WHEN 'm' THEN CAST(DATEPART(minute,@date) AS NVARCHAR(2)) WHEN 'ss' THEN RIGHT('00' + CAST(DATEPART(second,@date) AS NVARCHAR(2)),2) WHEN 's' THEN CAST(DATEPART(second,@date) AS NVARCHAR(2)) WHEN 'fff' THEN RIGHT('000' + CAST(DATEPART(millisecond,@date) AS NVARCHAR(3)),3) WHEN 'f' THEN CAST(DATEPART(millisecond,@date) AS NVARCHAR(3)) WHEN 'tt' THEN CASE WHEN DATEPART(hour,@date) >= 12 THEN N'PM' ELSE N'AM' END WHEN 't' THEN CASE WHEN DATEPART(hour,@date) >= 12 THEN N'P' ELSE N'A' END WHEN 'x' THEN CASE WHEN CAST(DAY(@date) AS INT) IN (1,21,31) THEN N'st' WHEN CAST(DAY(@date) AS INT) IN (3,23) THEN N'rd' ELSE N'th' END ELSE MIN(C) END FROM tokenizedString GROUP BY groupId ORDER BY MIN(N) FOR XML PATH('') ,TYPE ).value('(./text())[1]','nvarchar(50)') AS FormattedDate)
SELECT CHARINDEX('Mon','YYYY-MON-D')
IF (CHARINDEX ('MON',@DateValue)>0) SET @DateValue = REPLACE(@DateValue, 'MON', LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX ('MON',@DateValue COLLATE LATIN1_GENERAL_CS_AS)>0) SET @DateValue = REPLACE(@DateValue, 'MON', LEFT(UPPER(DATENAME(MM, @Datetime)),3))
SET @DateValue = @Format IF (CHARINDEX ('YYYY',@DateValue) > 0) SET @DateValue = REPLACE(@DateValue, 'YYYY', DATENAME(YY, @Datetime)) IF (CHARINDEX ('YY',@DateValue) > 0) SET @DateValue = REPLACE(@DateValue, 'YY', RIGHT(DATENAME(YY, @Datetime),2)) IF (CHARINDEX ('Month',@DateValue) > 0) SET @DateValue = REPLACE(@DateValue, 'Month', DATENAME(MM, @Datetime)) IF (CHARINDEX ('MON',@DateValue COLLATE LATIN1_GENERAL_CS_AS)>0) SET @DateValue = REPLACE(@DateValue, 'MON', LEFT(UPPER(DATENAME(MM, @Datetime)),3)) IF (CHARINDEX ('Mon',@DateValue) > 0) SET @DateValue = REPLACE(@DateValue, 'Mon', LEFT(DATENAME(MM, @Datetime),3)) IF (CHARINDEX ('MM',@DateValue) > 0) SET @DateValue = REPLACE(@DateValue,'MM', RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2)) IF (CHARINDEX ('M',@DateValue) > 0) AND (CHARINDEX('MA',@DateValue)=0) SET @DateValue = REPLACE(@DateValue,'M', CONVERT(VARCHAR,DATEPART(MM, @Datetime))) IF (CHARINDEX ('DD',@DateValue) > 0) SET @DateValue = REPLACE(@DateValue, 'DD', RIGHT('0'+DATENAME(DD, @Datetime),2)) IF (CHARINDEX ('D',@DateValue) > 0) SET @DateValue = REPLACE(@DateValue,'D', DATENAME(DD, @Datetime)) RETURN @DateValueEND
