REPEATABLE READ

Here's one that I run into every once in a while (posted here for anyone who may be able to benefit from it in the future).

As cool as CONVERT is, with all of its options for formatting dates/times, it's missing one rather large-ish formatting option: the ability to express a time (or datetime) as just an time with AM/PM designations, instead of 24 hour time.

So, I've baked up my own below:

CREATE FUNCTION dbo.GetAMPMFormattedTime(@input datetime)
RETURNS char(8)
AS
BEGIN

	DECLARE @dateString varchar(5)
	SET @dateString = LEFT(CONVERT(char(8),@input,108),5)

	DECLARE @hour varchar(2)
	DECLARE @minute varchar(2)
	
	SET @hour = LEFT(@dateString,2)
	SET @minute = SUBSTRING(@dateString,4,2)

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

	IF CAST(@hour as int) > 12 BEGIN
		SET @hour = @hour - 12
		SET @ampm = 'PM'
	END
			
	DECLARE @output char(8)
	SET @output = @hour + ':' + @minute + ' ' + @ampm

	RETURN @output
END
GO

/* Example invocation */
DECLARE @am datetime
DECLARE @pm datetime
DECLARE @noonish datetime

SET @am = '2005-01-27 10:00:00'
SET @pm = '2005-01-27 18:00:00'
SET @noonish = '2005-01-27 12:14:00'

SELECT dbo.GetAMPMFormattedTime(@am)
SELECT dbo.GetAMPMFormattedTime(@pm)
SELECT dbo.GetAMPMFormattedTime(@noonish)