Current Date and Time in SQL Server: GETDATE, SYSDATETIME and More

You can read the current date and time in SQL Server in eight ways. They differ in precision and in time zone. Pick the wrong one and your timestamps lose detail, or lose their meaning.

Current Date and Time in SQL Server: Eight Ways to Ask

Three of the eight are the same function under different names. GETDATE() is the T-SQL spelling. CURRENT_TIMESTAMP is the ANSI standard spelling. {fn NOW()} is an ODBC escape that SQL Server accepts. All three return a datetime. The rest add precision, a time zone offset, UTC, or the date alone.

The first script creates a small database for the examples. The second asks every function for its value in one query. SQL_VARIANT_PROPERTY reports the data type and scale of each value, so you can see what you get back.

IF DB_ID(N'DateTimeDemo') IS NULL CREATE DATABASE DateTimeDemo;
GO
USE DateTimeDemo;

SELECT f.FunctionName,
       CONVERT(nvarchar(40), f.Result, 121)       AS ReturnedValue,
       SQL_VARIANT_PROPERTY(f.Result, 'BaseType') AS DataType,
       SQL_VARIANT_PROPERTY(f.Result, 'Scale')    AS Scale
FROM (VALUES
    (N'GETDATE()',           CAST(GETDATE() AS sql_variant)),
    (N'CURRENT_TIMESTAMP',   CAST(CURRENT_TIMESTAMP AS sql_variant)),
    (N'{fn NOW()}',          CAST({fn NOW()} AS sql_variant)),
    (N'SYSDATETIME()',       CAST(SYSDATETIME() AS sql_variant)),
    (N'SYSDATETIMEOFFSET()', CAST(SYSDATETIMEOFFSET() AS sql_variant)),
    (N'GETUTCDATE()',        CAST(GETUTCDATE() AS sql_variant)),
    (N'SYSUTCDATETIME()',    CAST(SYSUTCDATETIME() AS sql_variant)),
    (N'CURRENT_DATE',        CAST(CURRENT_DATE AS sql_variant))
) AS f(FunctionName, Result);

Your values will differ, because they come from your clock. My server runs five and a half hours ahead of UTC, so the local and UTC rows are 5:30 apart. CURRENT_DATE runs on SQL Server 2025. The cast form shown later works on SQL Server 2008 and later. It’s the safer choice when code must run on several versions.

datetime and datetime2 Are Not the Same

A datetime keeps time in steps of 1/300 of a second. That’s why the milliseconds always end in 0, 3 or 7. A datetime2(7) holds 100 nanoseconds. For a report header, nobody cares about the difference.

For an event log, the difference matters. Two events inside the same few milliseconds can get identical datetime stamps. Then you can’t tell which came first. Use SYSDATETIME() or SYSUTCDATETIME() for anything you will sort or compare.

Once Per Statement, Not Once Per Row

These functions don’t tick while a query runs. Each function reads the clock once per statement and reuses that value for every row. The next query reads 100,000 rows and counts the distinct values for two of them.

SELECT COUNT(*)                       AS RowsRead,
       COUNT(DISTINCT t.NowValue)     AS DistinctGetdate,
       COUNT(DISTINCT t.PreciseValue) AS DistinctSysdatetime
FROM (SELECT TOP (100000) GETDATE() AS NowValue, SYSDATETIME() AS PreciseValue
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS t;

Every row got the same value from each function. Two different functions in one statement can still differ by a few milliseconds. The GETDATE, GETUTCDATE and SYSDATETIME rows in the first table show it. A new statement reads the clock again, even inside one batch. This script waits one second between two reads and measures the gap.

DECLARE @first datetime2(7) = SYSDATETIME();
WAITFOR DELAY '00:00:01';
DECLARE @second datetime2(7) = SYSDATETIME();
SELECT DATEDIFF(millisecond, @first, @second) AS MillisecondsApart;

The practical lesson: one INSERT stamps every row it adds with the same time. Don’t use the timestamp to order rows from the same statement. Use an identity column for that.

Defaults and Audit Columns

The best place to capture the current date and time is a DEFAULT constraint on the table. The application never has to remember to send a time, and every row gets one. My habit is a UTC datetime2 for the audit column. I add an offset column when local meaning matters, and a plain date for the order date.

DROP TABLE IF EXISTS dbo.OrderLog;
CREATE TABLE dbo.OrderLog (
    OrderID       int               IDENTITY(1,1) PRIMARY KEY,
    Item          nvarchar(60)      NOT NULL,
    OrderDate     date              NOT NULL CONSTRAINT DF_OrderLog_OrderDate DEFAULT CAST(SYSDATETIME() AS date),
    CreatedUtc    datetime2(3)      NOT NULL CONSTRAINT DF_OrderLog_CreatedUtc DEFAULT SYSUTCDATETIME(),
    CreatedOffset datetimeoffset(0) NOT NULL CONSTRAINT DF_OrderLog_CreatedOffset DEFAULT SYSDATETIMEOFFSET()
);
INSERT INTO dbo.OrderLog (Item) VALUES (N'Tomato seedlings'), (N'Herb planter');
SELECT OrderID, Item, OrderDate, CreatedUtc, CreatedOffset FROM dbo.OrderLog;

A default fires only on INSERT, and only when you leave the column out. An UPDATE never refreshes it. Both rows came from one INSERT, so both carry the same stamp. The UTC column and the offset column describe the same moment. Only the offset column says where the server was when it happened.

UTC and Time Zones

A plain datetime doesn’t record its time zone. A stored 14:00 can’t be placed once the server moves or a second one joins. Store UTC and convert when you display it. AT TIME ZONE does the conversion by zone name and applies daylight saving for you. It needs SQL Server 2016 or later.

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time'                 AS Pacific,
       SYSUTCDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS Eastern;

The zone is named Pacific Standard Time, yet the result shows -07:00. On this date, daylight saving applies, and SQL Server handled it. The second column shows the two-step form. The first AT TIME ZONE 'UTC' says what the value already is. The second says what you want. Leave the first off and SQL Server treats the UTC reading as Eastern time. The offset is right, but the hour is wrong.

Just the Date, and Other Formats

The old way to drop the time used DATEADD and DATEDIFF. Today, a cast to date says what it means. For a “today” filter, use a range. Start at the beginning of today and stop before the beginning of tomorrow. An index on the column can still help.

SELECT COUNT(*) AS OrdersTodayUtc
FROM dbo.OrderLog
WHERE CreatedUtc >= CAST(SYSUTCDATETIME() AS date)
  AND CreatedUtc <  DATEADD(day, 1, CAST(SYSUTCDATETIME() AS date));

It returns 2, the two rows inserted above. This counts UTC days. For a local day, filter on CreatedOffset or convert with AT TIME ZONE first. Display formats such as 09-02-2008 come up constantly. Convert only when you show the value, and keep the stored value a date type.

SELECT CONVERT(char(10), SYSDATETIME(), 110)  AS UsStyle,
       CONVERT(char(10), SYSDATETIME(), 23)   AS IsoStyle,
       CONVERT(varchar(11), SYSDATETIME(), 106) AS MonthName,
       CONVERT(char(8), SYSDATETIME(), 108)   AS TimeOnly,
       FORMAT(SYSDATETIME(), 'hh:mm tt')      AS AmPm,
       FORMAT(SYSDATETIME(), 'dd-MM-yyyy')    AS DayFirst;

FORMAT is handy for one value but slow over many rows, so prefer CONVERT in big queries.

You could argue that GETDATE() is good enough, because most old code uses it and it works. For a report header, that's true. It stops being true when two servers share data. It also fails when you need to know which of two events came first.

Which One Should You Use?

The right source for the current date and time in SQL Server depends on the column. For audit and log columns, use SYSUTCDATETIME(). It's precise, and it leaves no time zone question. When the local clock matters to the reader, such as a store's closing time, use SYSDATETIMEOFFSET(). For a printed report header, GETDATE() is fine.

CURRENT_TIMESTAMP is the portable choice when the same SQL must run on other database systems. Pick one spelling for each purpose and keep it across the codebase. Then a search finds every use, and nobody wonders whether two spellings differ.

What to Remember

Whenever you need the current date and time in SQL Server, ask two questions. Does the order of events matter, and does anyone read the value in another time zone? Two yeses mean a UTC datetime2, or a datetimeoffset.

Remember that each function reads the clock once per statement. Filter by range, convert time zones by name, and format only on display. Run the cleanup script below when you finish.

USE master;
GO
IF DB_ID(N'DateTimeDemo') IS NOT NULL
BEGIN
    ALTER DATABASE DateTimeDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DateTimeDemo;
END;

A timestamp is not a clock reading, it is a promise about when something happened.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.