SQL Server'daki DataTime tipindeki alanları formatlama ihtiyacımız olduğunda kolayca kullanabilecek bir fonksiyon.
Tek yapmanız gereken tarihi ve hangi formatta istedinizi söylemek. Uzun uzun convert kodları yazmaya gerek yok.
CREATE FUNCTION dbo.fn_FormatDate(@Type varchar(10),@Date Datetime)
RETURNS varchar(50)
AS BEGIN
/*
SELECT NULL [Type] ,CONVERT(varchar,GETDATE(),121) [Date]
UNION SELECT NULL ,dbo.fn_FormatDate(NULL,GETDATE())
UNION SELECT 'M' ,dbo.fn_FormatDate('M',GETDATE())
UNION SELECT 'L' ,dbo.fn_FormatDate('L',GETDATE())
UNION SELECT 'S' ,dbo.fn_FormatDate('S',GETDATE())
UNION SELECT 'SR' ,dbo.fn_FormatDate('SR',GETDATE())
UNION SELECT 'DL' ,dbo.fn_FormatDate('DL',GETDATE())
UNION SELECT 'D' ,dbo.fn_FormatDate('D',GETDATE())
UNION SELECT 'DR' ,dbo.fn_FormatDate('DR',GETDATE())
UNION SELECT 'DN' ,dbo.fn_FormatDate('DN',GETDATE())
UNION SELECT 'H' ,dbo.fn_FormatDate('H',GETDATE())
UNION SELECT 'LM' ,dbo.fn_FormatDate('LM',GETDATE())
*/
DECLARE @RetVal varchar(50)
SELECT @RetVal = NULL
IF @Date IS NULL
GOTO exit_proc
IF ISDATE(@Date) = 0
SELECT @Date = CONVERT(Datetime,'1900-01-01 00:00:00.000')
IF @Type = 'M' BEGIN -- Long2
SELECT @RetVal = dbo.fn_LeadZero(CONVERT(varchar,DAY(@Date)),2) + ' ' + DATENAME(month,@Date) + ' ' + CONVERT(varchar,YEAR(@Date))
GOTO exit_proc
END
IF @Type = 'L' BEGIN -- Long
SELECT @RetVal = CONVERT(varchar(20),@Date,104) + ' ' + DateName(weekday,@Date) + ', ' + CONVERT(varchar(5),@Date,108)
GOTO exit_proc
END
IF @Type = 'S' BEGIN -- Short
SELECT @RetVal = CONVERT(varchar(10),@Date,104) + ' ' + CONVERT(varchar(5),@Date,108)
GOTO exit_proc
END
IF @Type = 'DL' BEGIN -- Day With long format
SELECT @RetVal = CONVERT(varchar(20),@Date,104) + ' ' + DateName(weekday,@Date)
GOTO exit_proc
END
IF @Type = 'D' BEGIN -- Only day
SELECT @RetVal = CONVERT(varchar(10),@Date,104)
GOTO exit_proc
END
IF @Type = 'DR' BEGIN -- Day Reverse
SELECT @RetVal = CONVERT(varchar(10),@Date,102)
GOTO exit_proc
END
IF @Type = 'DN' BEGIN -- Only Dayname
SELECT @RetVal = DateName(weekday,@Date)
GOTO exit_proc
END
IF @Type = 'H' BEGIN -- Only Hour
SELECT @RetVal = CONVERT(varchar(5),@Date,108)
GOTO exit_proc
END
IF @Type = 'LM' BEGIN -- Long With Month Name
SELECT @RetVal = CONVERT(varchar(20),@Date,106) + ' ' + DateName(weekday,@Date) + ', ' + LEFT(CONVERT(varchar(30),@Date,108),5)
GOTO exit_proc
END
IF @Type = 'SR' BEGIN -- Short Reverse
SELECT @RetVal = CONVERT(varchar(10),@Date,120) + ' ' + CONVERT(varchar(5),@Date,108)
GOTO exit_proc
END
IF ISNULL(@Type,'') = '' BEGIN
SELECT @RetVal = CONVERT(varchar,@Date,121)
GOTO exit_proc
END
exit_proc:
IF ISNULL(@RetVal,'') = ''
SELECT @RetVal = CONVERT(varchar(10),@Date,104) + ' ' + CONVERT(varchar(5),@Date,108)
RETURN @RetVal
END
Function içerisine comment olarak eklenmiş olan örnek çalıştırma kodunu çalıştırırsak şöyle bir sonuç görebiliriz.
