MS SQL – Date format function
There may be times, where we need a custom date time format to be applied for the columns of a database table. Though there are different formats available which can not be used to our taste. So here I’m going to present you a MS SQL user defined scalar function. Here the function definition goes –
CREATE FUNCTION FormatDate
(
@ipDt DateTime, @Format varchar(30)
)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @time INT, @opDateTime VARCHAR(30)
IF(@Format = ‘dd/MM/yyyy HH:mm’)
BEGIN
SELECT @opDateTime = CONVERT(VARCHAR,@ipDt,103) +’ ‘+ LEFT(CONVERT(VARCHAR,@ipDt,108),5)
END
ELSE IF(@Format = ‘dd/MM/yyyy hh:mm tt’)
begin
SET @time = CAST(LEFT(CONVERT(VARCHAR,@ipDt,108),2) AS INT)
SELECT @opDateTime = CONVERT(VARCHAR,@ipDt,103) +’ ‘+ CASE WHEN @time > 12 THEN left(CONVERT(VARCHAR,DATEADD(hh,-12,@ipDt),108),5) ELSE LEFT(CONVERT(VARCHAR,@ipDt,108),5) END +’ ‘+RIGHT(CONVERT(VARCHAR,@ipDt,100),2)
END
ELSE IF(@Format = ‘MM/dd/yyyy HH:mm’)
BEGIN
SELECT @opDateTime = CONVERT(VARCHAR,@ipDt,101) +’ ‘+ LEFT (CONVERT(VARCHAR,@ipDt,108),5)
END
ELSE IF(@Format = ‘MM/dd/yyyy hh:mm tt’)
BEGIN
SET @time = CAST(LEFT(CONVERT(VARCHAR,@ipDt,108),2) AS INT)
SELECT @opDateTime = CONVERT(VARCHAR,@ipDt,101) +’ ‘+ CASE WHEN @time > 12 THEN left(CONVERT(VARCHAR,DATEADD(hh,-12,@ipDt),108),5) ELSE LEFT(CONVERT(VARCHAR,@ipDt,108),5) END +’ ‘+RIGHT(CONVERT(VARCHAR,@ipDt,100),2)
END
ELSE IF(@Format = ‘yyyy/MM/dd HH:mm’)
BEGIN
SELECT @opDateTime = CONVERT(VARCHAR,@ipDt,111) +’ ‘+ LEFT(CONVERT(VARCHAR,@ipDt,108),5)
END
ELSE IF(@Format = ‘yyyy/MM/dd hh:mm tt’)
BEGIN
SET @time = CAST(LEFT(CONVERT(VARCHAR,@ipDt,108),2) AS INT)
SELECT @opDateTime = CONVERT(VARCHAR,@ipDt,111) +’ ‘+ CASE WHEN @time > 12 THEN LEFT(CONVERT(VARCHAR,DATEADD(hh,-12,@ipDt),108),5) ELSE LEFT(CONVERT(VARCHAR,@ipDt,108),5) END +’ ‘+RIGHT(CONVERT(VARCHAR,@ipDt,100),2)
END
RETURN @opDateTime
END
This can be invoked as follows –
select dbo.FormatDate(‘2009-09-22 13:58:27.297′,’dd/MM/yyyy HH:mm’)
select dbo.FormatDate(‘2009-09-22 13:58:27.297′,’dd/MM/yyyy hh:mm tt’)
select dbo.FormatDate(‘2009-09-22 13:58:27.297′,’MM/dd/yyyy HH:mm’)
select dbo.FormatDate(‘2009-09-22 13:58:27.297′,’MM/dd/yyyy hh:mm tt’)
select dbo.FormatDate(‘2009-09-22 13:58:27.297′,’yyyy/MM/dd HH:mm’)
select dbo.FormatDate(‘2009-09-22 13:58:27.297′,’yyyy/MM/dd hh:mm tt’)
Or you can use this with the table retrieval operations too.
Hope this helps you; also you can modify the function to your taste that accommodates to a particular format.
