CODEWITHSUNDEEP
sql-server-2008time
32bitfloat
32bitfloat

Reputation: 771

A simpler way to get the current time in SQL Server 2008

I need to store the current time (format 'hi') as char(4) in a table (is created and used by another program, I can't change it) and now wondered what is a suitable way to retrieve it via SQL. I know MySQL is not that standard-orientated, but I thought it could be something similiar to DATE_FORMAT(NOW(), 'Hi').
The code below which I found works, but feels little intricately.

SELECT CAST(DATEPART(hour, GETDATE()) AS nvarchar)+CAST(DATEPART(minute, GETDATE()) AS nvarchar);

Is there a better way to achieve this?

Upvotes: 3

Views: 12136

Answers (2)

Ivan Golović
Ivan Golović

Reputation: 8832

Following gives the same result:

SELECT LEFT(STUFF(CONVERT(NCHAR(8), GETDATE(), 8), 3, 1,''), 4)

It seems to have approximately the same performance as the method from question. I tested it like this:

DECLARE @i INT = 0,
        @dto1 DATETIME2,
        @dto2 DATETIME2

SET @dto1 = SYSDATETIME()
WHILE @i < 100000
BEGIN
    PRINT LEFT(STUFF(CONVERT(NCHAR(8), GETDATE(), 8), 3, 1,''), 4)
    SET @i = @i + 1
END 
SET @dto2 = SYSDATETIME()

SELECT DATEDIFF(MILLISECOND, @dto1, @dto2)

Upvotes: 3

Taryn
Taryn

Reputation: 247610

Sql Server 2008 has a time datatype:

select replace(left(cast(getdate() as time), 5), ':', '')

Upvotes: 3

Related Questions