Sql-server – Why does formatting a time with AM/PM specifier return null

formatsql serversql server 2014t-sql

I have a query where a formatted expression is returning null:

declare @FormatString nvarchar(max) = N'hh:mm tt'
select M_STARTTIME as [Memo Time 1]
    , convert(time,M_STARTTIME) as [Memo Time 2]
    , format(convert(time,M_STARTTIME), @FormatString) as [Formatted Memo Time]
    , convert(time, getdate()) as [Current Time]
    , format(getdate(), @FormatString) as [Formatted Cur Time]
from MEMO
where M_STARTTIME is not null

M_STARTTIME holds an nvarchar field with values such as '10:27', '13:01', '8:25' and null. In this query:

  1. [Memo Time 1] displays the string field
  2. [Memo Time 2] displays the memo time as a time field correctly
  3. [Formatted memo time] returns a null – for all rows
  4. [Current Time] shows the current time
  5. [Formatted Cur Time] shows the current time formatted using the format specifier.

According to MSDN, FORMAT returns null for errors other than a culture that is not valid. For example, null is returned if the value specified in format is not valid. I have not been able to identify where the error is though (I'm assuming i've missed something really obvious, or the issue is something really subtle).

Converting the nvarchar to a time should not be the problem, the time displays correctly in column 2. Formatting using the time specifier should not be the problem, since I use the same time specifier on another time value.

Interestingly, according to example D in the MSDN link (time values) that time format specifier should cause an error because the ':' is not escaped. If that is the case why is [Formatted Cur Time] working? Also, if I escape the format specifier with:

declare @FormatString nvarchar(max) = N'hh\:mm tt'

then the [Formatted Memo Time] still returns null and the [Formatted Cur Time] still returns the current time.

dbcc checktable (MEMO)

…identified no issues

What is going on?

Best Answer

Edit: tt is a valid format specifier for datetime, but not time. The \ is also optional in datetime, but not time. Example at end.

I think the tt is the problem
I am not finding tt for time formatting - what is it supposed to do?

Did you try without using @FormatString?
Below works 

declare @FormatString nvarchar(200) = N'hh\:mm'
declare @TimeChar nvarchar(200) = N'10:27'
select @FormatString
select @TimeChar
select convert(time, @TimeChar)
select format(convert(time, @TimeChar), N'hh\:mm')
select format(convert(time, @TimeChar), @FormatString)

Example:

select format(convert(time    , '10:27'), N'hh\:mm')   -- returns 10:27
     , format(convert(time    , '10:27'), N'hh:mm')    -- returns NULL (need to escape ':'):
     , format(convert(time    , '10:27'), N'hh:mm tt') -- returns NULL
     , format(convert(datetime, '10:27'), N'hh:mm tt') -- returns 10:27
Related Question