Time conversion?

Transact-SQL
1

Hello, we have a table and two of the columns are inpdate and inptime. Inpdate is of type datetime2, inptime is of type time.

I know that I can CONVERT (VARCHAR, inpdate, 112) to turn 3rd August 2022 into 20220803.....is there an equivalent for time? I'd like 18:47:23 to be returned as 184723.

Thanks!

2

can you try this
SELECT FORMAT(inptime,'hhmmss') AS NewFormatTime from TableName;

1 Like
3

Genius, many thanks!

1 Like
4

I would not recommend using FORMAT here - FORMAT is extremely slow. A simple convert with a replace would be much more performant.

REPLACE(CONVERT(CHAR(10), inptime, 108), ':', '')
2 Likes
5

@rasta_pickles
I'll very strongly second the recommendation that @jeffw8713 made. Just forget that FORMAT even exists. I have done many demonstrations to prove that it is at least 17 times slower (even on today's super high speed machines) than just about any concoction you might dream of doing in CONVERT and I've seen it be up to 40 times slower.

1 Like
6

Heh... speaking of those tests, my article on the subject of not using FORMAT just came out on SQLServerCentral.com. Here's the link...

https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format

1 Like