Why does formatting a time with AM/PM specifier return null?
1
vote
3
answers
3556
views
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?
Asked by Jamie Alford
(179 rep)
Mar 23, 2016, 06:33 AM
Last activity: Nov 14, 2019, 05:28 PM
Last activity: Nov 14, 2019, 05:28 PM