How to print GETDATE() in SQL Server with milliseconds in time?

sql

datetime

timestamp

performance-tracking

byAlex Kataev·Sep 23, 2024

For quick millisecond precision in SQL Server, use the FORMAT function as follows:

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff') AS CurrentTimeWithMillis;

This wraps the GETDATE() function with FORMAT, employing the 'yyyy-MM-dd HH:mm:ss.fff' format string, capturing milliseconds (fff) along with the more standard date-time elements.

Enhancing precision

Using SYSDATETIME() over GETDATE() provides a higher level of precision. You can convert and format the current timestamp to include milliseconds as follows:

SELECT CONVERT(varchar, SYSDATETIME(), 121) AS CurrentTimeWithMillis;

Increasing precision adds just enough spice to keep things interesting!

Navigate the PRINT roadblock

The PRINT statement won't show milliseconds as is. Instead of a detour, let's use SELECT with our conversion techniques:

PRINT CONVERT(varchar, SYSDATETIME(), 121);  -- "I don't do milliseconds" - PRINT
SELECT CONVERT(varchar, SYSDATETIME(), 121);  -- "Watch this!" - SELECT

Fun fact: SQL statements love a chat, especially when it's precise!

Making smart datatype choices

When storing time values of high precision, use the datatype datetime2(3) like a pro:

DECLARE @Time datetime2(3) = SYSDATETIME();
SELECT @Time;

Because losing milliseconds in the datasphere is a coder's version of sleepwalking.

Calculations: handle with care

While performing time calculations, bigint is your safe bet to avoid overflows. You can thank us later!

SELECT CAST(DATEDIFF(SECOND, time_start, SYSDATETIME()) AS bigint) * 1000 AS MillisecondsPassed
FROM your_timed_events_table;

Because, in SQL world, prevention is every bit as exciting as multiplication!

Practical use-cases

Milliseconds come in handy in real-world application scenarios:

Performance tracking: Determining which block of code is taking longer than expected.
Data synchronization: Ensuring systems are in sync down to the millisecond.
Sequencing events: Determining the order of events in a time-sensitive system.
Error tracking: Logging the exact time an error occurred can be crucial when debugging.