Technical Article

Date/Time formatting function

,

T-SQL analog for Visual Basic FORMAT function.

Created for MSSQL 2000.

Works regardless of language settins on server or client side!

fn_format('YYYY/DD/MM HH:MI','2002-03-13 12:00') = '2002/13/03 12:00'

fn_format('DD.MM.YY','2002-03-13 12:00:00') = '13.03.02'

fn_format('HH:MI:SS','2002-03-13 12:00:00') = '12:00:00'

CREATE FUNCTION fn_format (@fmt varchar(100)='YYYYMMDD HH:MI:SS.LL', @date datetime)  
RETURNS varchar(100) AS  
BEGIN 
	DECLARE @datestring varchar(50), @i tinyint, @s char(1), @ss char(1), @s2 varchar(10)
	SET @datestring = ''
	SET @i = 1
	SET @s = left(@fmt,1)
	WHILE @i < DATALENGTH(@fmt)
	BEGIN
		SET @ss = Substring(@fmt,@i,1)
		If @ss <> @s
		BEGIN
			SET @fmt = substring(@fmt,1,@i-1) + ''',''' + substring(@fmt,@i,DATALENGTH(@fmt))
			SET @i = @i + 3
			SET @s = @ss
		END
		SET @i = @i + 1
	END
	SET @fmt=''''+@fmt+''''
	declare cur cursor read_only for select value from system.dbo.fn_split(@fmt,',')
	open cur
	FETCH NEXT FROM cur INTO @s2
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			IF @s2 = 'YY' SET @datestring = @datestring + right(cast(datepart(year,@date) as char(4)),2)
			ELSE IF @s2 = 'YYYY' SET @datestring = @datestring + right(cast(datepart(year,@date) as char(4)),4)
			ELSE IF @s2 = 'MM' SET @datestring = @datestring + right('0'+ltrim(str(datepart(month,@date))),2)
			ELSE IF @s2 = 'DD' SET @datestring = @datestring + right('0'+ltrim(str(datepart(day,@date))),2)
			ELSE IF @s2 = 'HH' SET @datestring = @datestring + right('0'+ltrim(str(datepart(hour,@date))),2)
			ELSE IF @s2 = 'NN' SET @datestring = @datestring + right('0'+ltrim(str(datepart(minute,@date))),2)
			ELSE IF @s2 = 'SS' SET @datestring = @datestring + right('0'+ltrim(str(datepart(second,@date))),2)
			ELSE IF @s2 = 'LL' SET @datestring = @datestring + ltrim(str(datepart(millisecond,@date)))
			ELSE		   SET @datestring = @datestring + @s2
		END
		FETCH NEXT FROM cur INTO @s2
	END
	close cur
	deallocate cur
	RETURN (@datestring)
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating