SQL Server da Tarih Formatlama

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.

Type	Date
	2010-04-29 18:56:30.610
D	29.04.2010
DL	29.04.2010 Thursday
DN	Thursday
DR	2010.04.29
H	18:56
L	29.04.2010 Thursday, 18:56
LM	29 Apr 2010 Thursday,  18:56
M	29 April 2010
S	29.04.2010 18:56
SR	2010-04-29 18:56