Date String format with optional Timestamp
I find it so annoying to format dates. Sometimes I want to include the timestamp, but if it is a midnight time stamp, the time is irrelevant. Sending in a datetime will return either the date string or a date string with a timestamp up to the seconds with either AM or PM.
CREATE Function dbo.fn_DateTimeString(@Date datetime)
Returns varchar(25)
AS
BEGIN
Declare @m varchar(2), @y varchar(4), @d varchar(2),
@h varchar(2), @mi varchar(2), @sec varchar(2), @AMPM varchar(2),
@NewDate varchar(25)
Select @m = datepart(m, @Date), @y = datepart(yy, @Date), @d = datepart(d, @Date)
If datepart(hh, @Date) = 0 AND datepart(mi, @Date) = 0 AND datepart(s, @Date) = 0
BEGIN
Set @NewDate = @m + '/' + @d + '/' + @y
END
ELSE
BEGIN
Select @h = datepart(hh, @Date),
@AMPM = CASE WHEN @h >= 12 THEN 'PM' ELSE 'AM' END,
@h = CASE WHEN @h = 0 THEN 12 WHEN @h > 12 THEN @h - 12 ELSE @h END,
@mi = CASE WHEN datepart(mi, @Date) < 10 THEN ('0' + convert(varchar(1), datepart(mi, @Date))) ELSE convert(varchar(2), datepart(mi, @Date)) END,
@sec = CASE WHEN datepart(s, @Date) < 10 THEN ('0' + convert(varchar(1), datepart(s, @Date))) ELSE convert(varchar(2), datepart(s, @Date)) END
Set @NewDate = @m + '/' + @d + '/' + @y + ' ' + @h + ':' + @mi + ':' + @sec + ' ' + @AMPM
END
Return @NewDate
END