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:
- [Memo Time 1] displays the string field
- [Memo Time 2] displays the memo time as a time field correctly
- [Formatted memo time] returns a null - for all rows
- [Current Time] shows the current time
- [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?