SQL Server: Return Date Only from GETDATE

GETDATE() carries the time down to the millisecond, which breaks a WHERE clause built to match a single day and clutters a report column meant to show a calendar date. SQL Server 2022 has two ways to drop the time: cast the value to the date type, or convert it to a formatted string with a style code.

SELECT GETDATE() AS CurrentDateTime;

The simplest way to return date only

Casting the result to the date data type keeps the value as a date, not a string, so it still sorts, compares, and joins like a date:

SELECT CAST(GETDATE() AS DATE) AS CurrentDate;

CONVERT(date, GETDATE()) produces the same result. Use CAST when you only need the type conversion, since it takes no style argument and reads slightly clearer for that one job. Microsoft's CAST and CONVERT reference[1] puts the behavior in one line: converting datetime to date drops the portion that does not apply. What comes back is a value of the date type, the same type you would give a column meant to hold dates in a table definition, and comparing it against another date needs no conversion on either side.

The CONVERT method

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

CONVERT can also produce a formatted string, when the destination is a report, a log line, or an export rather than another SQL expression. The style code controls the layout:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS FormattedDate;

Breaking the call down:

• VARCHAR(10) is the target data type;
• (10) caps the result at 10 characters, which is exactly what a mm/dd/yyyy string needs;
• GETDATE() is the value being converted;
• 101 is the style code that picks the layout.

Size the target to the style you pick. A conversion to varchar that does not fit is truncated rather than rejected, so Mar 14, 2026 at style 107 needs at least VARCHAR(12), and Microsoft's advice on the same page is to choose a char or varchar length appropriate to the date parts you want to keep.

Each style code below applied to the same GETDATE() result, 2026-03-14 13:52:07.113, per the same reference[1]:

Styles 108 and 109 are missing from this table because neither returns a date-only result: 108 returns a time (hh:mi:ss), and 109 returns a date with the time down to the millisecond (mon dd yyyy hh:mi:ss:mmmAM).

Filtering and grouping rows by date only

A table that stores an order timestamp is the usual place this comes up. The rest of this section runs against six orders spread over three days:

CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    OrderDate DATETIME NOT NULL
);

INSERT INTO Orders VALUES
    (1, '2026-03-14 09:12:00'),
    (2, '2026-03-14 17:45:30'),
    (3, '2026-03-14 23:59:59'),
    (4, '2026-03-15 08:00:00'),
    (5, '2026-03-15 20:15:00'),
    (6, '2026-04-02 10:00:00');

Wrapping the column the same way as GETDATE() looks correct and returns the right rows:

SELECT OrderId, OrderDate
FROM Orders
WHERE CAST(OrderDate AS DATE) = '2026-03-14';

The problem is what it does to an index on OrderDate. Wrapping a column in CAST or CONVERT makes the predicate non-SARGable, Microsoft's term for a search argument an index can be used against: per Microsoft's own troubleshooting guidance[2], applying a function to a column in a WHERE clause generally prevents the optimizer from seeking that index, and it falls back to a scan instead. On a small table that costs nothing. On a table with millions of rows, it is the difference between a query that returns instantly and one that reads every page.

The SARGable version filters on the raw column and expresses "one calendar day" as a range instead, the same range technique used to filter dates with BETWEEN:

SELECT OrderId, OrderDate
FROM Orders
WHERE OrderDate >= '2026-03-14'
  AND OrderDate < '2026-03-15';

Both queries return the same three rows. Only the second one lets the optimizer seek OrderDate if it is indexed.

Grouping is a different case, because a GROUP BY already has to look at every row in the set it is aggregating, so wrapping the grouping column costs nothing extra. The same principle applies to a running total or a rank computed per day with a window function: filter the range on the raw column first, then partition or group on the cast value.

SELECT CAST(OrderDate AS DATE) AS OrderDay, COUNT(*) AS Orders
FROM Orders
WHERE OrderDate >= '2026-03-01' AND OrderDate < '2026-04-01'
GROUP BY CAST(OrderDate AS DATE);

Filter the range first with the raw column, as shown above, then cast only the column you group or select by. That keeps the index seek and still gives you one row per day.

Where date formatting belongs

CAST and CONVERT answer two different questions, and mixing them up is the usual source of a query that formats dates one way in one place and another way somewhere else. Microsoft's CAST and CONVERT reference[1] and FORMAT reference[3] draw the line directly: use CAST or CONVERT for a general data type conversion, and use FORMAT only for locale-aware display formatting of a date or number as a string. FORMAT relies on the presence of the .NET Common Language Runtime and, per the same page, can't be remoted, which is worth knowing before reaching for it inside a query that already crosses servers.

The more durable line is between the query layer and the application layer. Returning a date value from CAST, with no style code attached, keeps the query reusable: the application, the report tool, or the export can format that value however the reader's locale requires, without a second round trip to the database. Converting to a specific string inside the query only makes sense when the consumer genuinely cannot format the value itself, such as a flat-file export or a log line that has to be human-readable as plain text. Where you do format inside SQL, keep the style code consistent across the queries that feed the same report, so a date column doesn't read 03/14/2026 in one place and 2026-03-14 in another.

DbSchema's SQL editor connects directly to a SQL Server database, so you can run the queries above against your own schema, check what a style code returns and confirm that a range filter still seeks the index, before any of it goes into application code. Connecting and the SQL editor are in the free Community Edition: download it at https://dbschema.com/download.html.

FAQ

Why does SELECT CONVERT(date, GETDATE()) return a date instead of a string?

Because date is a data type, not a format. CONVERT produces a formatted string only when the target type is a character type such as VARCHAR, and the style code then chooses the layout of that string.

Does casting GETDATE() to date affect an existing index?

Not on GETDATE() itself, since it returns a fresh value on every call and is never indexed directly. It matters on a stored column: casting or converting a datetime column to date inside a WHERE clause stops an index on that column from being seeked, for the SARGability reason described above.

Sources

1. CAST and CONVERT (Transact-SQL) - Microsoft Learn
2. Investigate and resolve SARGability issues - Troubleshoot high CPU usage issues - Microsoft Learn
3. FORMAT (Transact-SQL) - Microsoft Learn