CODEWITHSUNDEEP
sqlsql-serverdatetimeformat
akkapolk
akkapolk

Reputation: 594

FORMAT([Time], 'hh:mm') returns NULL

I have a Calendar table with the following structure:

CalendarID    nvarchar(13)
Date          date
Time          time(7)

with data such as:

Calendar table data

Formatting the time column as hhmm works:

SELECT [CalendarID]
     , FORMAT([Date], 'ddd, MMM dd, yyyy') AS [DATE]
     , FORMAT([Time], 'hhmm') AS [Time]
FROM [dbo].[Calendar]

select time format (hhmm)

But formatting as hh:mm does not work and displays NULL:

SELECT [CalendarID]
     , FORMAT([Date], 'ddd, MMM dd, yyyy') AS [DATE]
     , FORMAT([Time], 'hh:mm') AS [Time]
FROM [dbo].[Calendar]

select time format (hh:mm)

How can I format it as hh:mm?

Upvotes: 2

Views: 1987

Answers (5)

EricI
EricI

Reputation: 3814

The FORMAT() function doesn't work well with the TIME datatype. The solution is to first convert the TIME to a DATETIME before formatting...

DECLARE @CurrentTime AS TIME = GETDATE();

The Problem: Format returns NULL for the current time when you pass it a TIME.

SELECT @CurrentTime AS TimeUnformatted, FORMAT(@CurrentTime, 'hh:mm tt') AS CurrentTime;

The Solution: Cast the time as a DATETIME before formatting.

SELECT FORMAT(CAST(@CurrentTime AS DATETIME), 'hh:mm tt') AS CurrentTime;

Upvotes: -1

Jason Uithol
Jason Uithol

Reputation: 29

It appears to be very picky about input types. This will return NULL:

declare @theNow as time
set @theNow = cast(getdate() as time)
print format(@theNow, N'hh\:mm tt')

whereas this will print your expected result string:

declare @theNow as datetime
set @theNow = cast(getdate() as datetime)
print format(@theNow, N'hh\:mm tt')

I'm still trying to work out the details myself.

Upvotes: 2

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

Try it in this way:

FORMAT([Time], N'hhmm') AS [Time]

or 

FORMAT([Time], N'hh\:mm') AS [Time]

Upvotes: 3

Seyhmus Gokcen
Seyhmus Gokcen

Reputation: 248

You may want to check the docs about date and datetime

https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017

After Larnu's warning

Update:

From the above link you can format the time like that;

SELECT FORMAT(cast(GETDATE() as time), N'hh\:mm\:ss');

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272236

From the docs:

FORMAT relies upon CLR formatting rules, which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type.

This should work:

SELECT FORMAT(CAST('09:00:00.1234567' AS TIME(7)), 'hh\:mm')              -- 09:00
SELECT FORMAT(CAST('09:00:00.1234567' AS TIME(7)), 'hh\:mm\:ss\.fffffff') -- 09:00:00.1234567

Upvotes: 3

Related Questions