Samuele E. Locatelli
42 lines
1.9 KiB
Transact-SQL
42 lines
1.9 KiB
Transact-SQL
-----------------------------------------------------------------------------
|
||
-- SQL Server date formatting function – convert datetime to string
|
||
-----------------------------------------------------------------------------
|
||
-- SQL datetime functions
|
||
-- SQL Server date formats
|
||
-- T-SQL convert dates
|
||
-- Formatting dates sql server
|
||
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
|
||
RETURNS VARCHAR(32)
|
||
AS
|
||
BEGIN
|
||
DECLARE @StringDate VARCHAR(32)
|
||
SET @StringDate = @FormatMask
|
||
IF (CHARINDEX ('YYYY',@StringDate) > 0)
|
||
SET @StringDate = REPLACE(@StringDate, 'YYYY',
|
||
DATENAME(YY, @Datetime))
|
||
IF (CHARINDEX ('YY',@StringDate) > 0)
|
||
SET @StringDate = REPLACE(@StringDate, 'YY',
|
||
RIGHT(DATENAME(YY, @Datetime),2))
|
||
IF (CHARINDEX ('Month',@StringDate) > 0)
|
||
SET @StringDate = REPLACE(@StringDate, 'Month',
|
||
DATENAME(MM, @Datetime))
|
||
IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
|
||
SET @StringDate = REPLACE(@StringDate, 'MON',
|
||
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
|
||
IF (CHARINDEX ('Mon',@StringDate) > 0)
|
||
SET @StringDate = REPLACE(@StringDate, 'Mon',
|
||
LEFT(DATENAME(MM, @Datetime),3))
|
||
IF (CHARINDEX ('MM',@StringDate) > 0)
|
||
SET @StringDate = REPLACE(@StringDate, 'MM',
|
||
RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
|
||
IF (CHARINDEX ('M',@StringDate) > 0)
|
||
SET @StringDate = REPLACE(@StringDate, 'M',
|
||
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
|
||
IF (CHARINDEX ('DD',@StringDate) > 0)
|
||
SET @StringDate = REPLACE(@StringDate, 'DD',
|
||
RIGHT('0'+DATENAME(DD, @Datetime),2))
|
||
IF (CHARINDEX ('D',@StringDate) > 0)
|
||
SET @StringDate = REPLACE(@StringDate, 'D',
|
||
DATENAME(DD, @Datetime))
|
||
RETURN @StringDate
|
||
END |