Sample Header Ad - 728x90

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