My shared items

 

Saturday, September 1, 2007

How to Get AM PM Formatted DateTime in SQL Server

The Following Function is used to Gets the AM Or PM Formatted DateTime for the Given Date


CREATE FUNCTION GetAMPMFormat(@inputDate DATETIME)
RETURNS char(25)
AS
BEGIN

DECLARE @time varchar(5)
SET @time = CONVERT(char(10), @inputDate, 108)

DECLARE @date varchar(10)
SET @date = CONVERT(char(10), @inputDate, 101)

DECLARE @hour varchar(2)
DECLARE @minute varchar(2)

SET @hour = LEFT(@time,2)
SET @minute = SUBSTRING(@time,4,2)

DECLARE @amOrPm char(2)
SET @amOrPm = 'AM'

IF CAST(@hour as int) = 0 BEGIN
SET @hour = 12
SET @amOrPm = 'AM'
END

ELSE IF CAST(@hour as int) = 12 BEGIN
SET @amOrPm = 'PM'
END

ELSE IF CAST(@hour as int) > 12 BEGIN
SET @hour = @hour - 12
SET @amOrPm = 'PM'
END

DECLARE @outputDate char(25)
SET @outputDate = @date + ' ' + @hour + ':' + @minute + ' ' + @amOrPm

RETURN @outputDate
END


For Example you maye use like

PRINT dbo.GetAMPMFormattedDateTime('01-08-2007 20:10')

it will prints
01/08/2007 8:10 PM

No comments: