1

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?

2

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
  • Yes, i tried without @FormatString - my original version was just a string literal. I moved to a variable to ensure I was using exactly the same format specifier and I wasn't missing a simple typo while tired. – Jamie Alford Mar 23 '16 at 23:47
  • It turns out you are right. the tt format specifier (AM/PM) is valid for the datetime datatype but not the time datatype. I'm shaking my head at that one. – Jamie Alford Mar 24 '16 at 0:13
  • I've edited your answer to clarify and added an example. – Jamie Alford Mar 24 '16 at 0:22
1

tt is the indicator for AM/PM

Paparazzi, I replaced your last two lines as follows:

select format(convert(datetime, @TimeChar), N'hh:mm tt')
select format(convert(datetime, @TimeChar), @FormatString)

This returns 10:27 AM

  • tt is only valid for datetime - but maybe that works for the OP – paparazzo Mar 23 '16 at 19:52
  • it works when formatting getdate(), it doesn't work when formatting convert(time, m_starttime). That's what annoys me most - if it worked for both, or failed for both it would make a kind of sense. What is the difference between GetDate() and convert(time, m_starttime) that is causing a null? – Jamie Alford Mar 23 '16 at 23:49
  • GETDATE() returns a datetime result. The code that worked for me changed your original "time" type to a "datetime". It appears that you will need to use datetime in order to obtain the AM/PM designation. – Gary Mar 24 '16 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.