SQL Server’da Tarih Formatlama Fonksiyonu

Herkese merhaba,

Bu yazıda SQL Server’da tarih formatlama fonksiyonundan bahsedeceğim.

Konu ile alakalı örneğe bu konu hakkında araştırma yaparken buldum. Umarım işinize yarar.

CREATE FUNCTION [dbo].[FormatDate]
(
    @Datetime DATETIME,
    @FormatMask VARCHAR(64)
)
RETURNS VARCHAR(64)
AS
BEGIN
    DECLARE @StringDate VARCHAR(64),
            @Month VARCHAR(12),
            @MON VARCHAR(3),
            @24H BIT;
    SET @StringDate = @FormatMask;
    SET @24H = 1;
    --Special Codes (Codes that return more letters) 
	----------------------------------------------------------------------------------------------------- 
    IF (CHARINDEX('Month', @StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'Month', '|Q|');
    IF (CHARINDEX('MON', @StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'MON', '|E|');
    IF (CHARINDEX('AMPM', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
    BEGIN
        SET @StringDate = REPLACE(@StringDate, 'AMPM' COLLATE SQL_Latin1_General_CP1_CS_AS, '|X|');
        SET @24H = 0;
    END;
    IF (CHARINDEX('ampm', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
    BEGIN
        SET @StringDate = REPLACE(@StringDate, 'ampm' COLLATE SQL_Latin1_General_CP1_CS_AS, '|x|');
        SET @24H = 0;
    END;
    --DATE ------------------------------------------------------------------------------------------------
    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('MM', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
        SET @StringDate
            = REPLACE(
                         @StringDate,
                         'MM' COLLATE SQL_Latin1_General_CP1_CS_AS,
                         RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @Datetime)), 2)
                     );
    IF (CHARINDEX('M', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
        SET @StringDate
            = REPLACE(@StringDate, 'M' COLLATE SQL_Latin1_General_CP1_CS_AS, 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));
    --TIME -----------------------------------------------------------------------------------------
    IF (CHARINDEX('h', @StringDate) > 0)
    BEGIN
        DECLARE @Hour INT;
        SET @Hour = DATEPART(HH, @Datetime);
        SELECT @Hour = CASE
                           WHEN @24H = 0 THEN
                               CASE
                                   WHEN @Hour > 12 THEN
                                       @Hour - 12
                                   ELSE
                                       @Hour
                               END
                           ELSE
                               @Hour
                       END;
        IF (CHARINDEX('hh', @StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'hh', RIGHT('0' + CONVERT(VARCHAR, @Hour), 2));
        IF (CHARINDEX('h', @StringDate) > 0)
            SET @StringDate = REPLACE(@StringDate, 'h', @Hour);
    END;
    IF (CHARINDEX('mm', @StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'mm', RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, @Datetime)), 2));
    IF (CHARINDEX('m', @StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'm', DATEPART(mi, @Datetime));
    IF (CHARINDEX('ss', @StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'ss', RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, @Datetime)), 2));
    IF (CHARINDEX('s', @StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 's', DATEPART(ss, @Datetime));
    -- Special Codes -- Must be done last because they replace the code with letters that could be seen as another code ('m') 
	--------------------------------------------------------------------------------------------------------------------------
    IF (CHARINDEX('|Q|', @StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, '|Q|', DATENAME(MM, @Datetime));
    IF (CHARINDEX('|E|', @StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, '|E|', LEFT(DATENAME(MM, @Datetime), 3));
    IF (CHARINDEX('|X|', @StringDate) > 0)
    BEGIN
        DECLARE @AMPM VARCHAR(2);
        IF DATEPART(HH, @Datetime) > 12
            SET @AMPM = 'pm';
        ELSE
            SET @AMPM = 'am';
        IF CHARINDEX('|X|', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0
            SET @AMPM = UPPER(@AMPM);
        SET @StringDate = REPLACE(@StringDate, '|X|', @AMPM);
    END;
    RETURN @StringDate;
END;

--Örnek işlem 

SELECT [dbo].[FormatDate]('1/31/09 22:15:45:222', 'Month MON MM/DD/YYYY @ hh:mm:ss ampm');

Sorgu için bir örnek yapıp fonksiyonu çalıştırdığımız zaman aşağıdaki sonucu göreceksiniz.

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.