Technical Article

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

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating