SQL Server formating time returns null values-CodePudding

In my table I have two fields with time(2) type: startTime and endTime.

SELECT startTime, endTime FROM ...

gives me time formated like this: 13:00:00. I want to display time in my app without seconds, for example 13:00. I tried: SELECT FORMAT(startTime, 'hh:mm') as startTime FROM ... but when I executed it I got null values:

startTime
_____________
NULL
NULL

It's weird because If I select my data normally, like this SELECT startTime FROM ... I get:

startTime
___________
12:00:00
13:00:00

What Am I doing wrong?

CodePudding user response：

You need to escape the colon : as \:. For example:

SELECT FORMAT(startTime, 'hh\:mm') as startTime FROM t

Result:

startTime
---------
12:00
13:00

See example at db<>fiddle.

CodePudding user response：

Rather than format() which has terrible performance, consider convert(...) .

Example

declare @t time(2) = '12:00'

Select convert(varchar(5),@t,108)

Results

12:00