How to return only the Date from a SQL Server DateTime datatype

Alternatives for early versions

Flirting with CONVERT function

For those SQL Server versions that distinctly remember the 90s, CONVERT dives in for the rescue:

-- Like a Spotify playlist on repeat (returns YYYY-MM-DD)
SELECT CONVERT(VARCHAR(10), GETDATE(), 120)


Just remember, VARCHAR and local settings go together like pineapple on pizza, so proceed with caution!

Romance with DATEADD and DATEDIFF

When you need to mingle data types without strings attached, DATEADD and DATEDIFF are your perfect blind date:

-- The Cinderella of SQL: home by midnight
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))


This steamy encounter ensures a datetime result which winks at midnight. Plus, it's like the United Nations — it speaks all languages (locale-agnostic).

Watch out for potholes!

String conversion: handle with care!

We all love a quick fix, but remember the old adage about all that glitters not being gold? Well, this applies to CONVERT(VARCHAR(10), ...). Unexpected side smiles await if you ignore language settings.

Time zone: time to gear up!

Time zones can make date and time more erratic than a cat on catnip! Exercise caution since SQL Server sips DateTime in UTC.

Performance: you snooze, you lose!

Any function-oriented festival on indexed columns could paint a query performance slower than a snail on vacation. Beware the tortoise syndrome!

Afterparty tricks for efficient dating

Table indexing: a premeditated affair

Throwing functions on indexed columns in where clauses or joins can ruin your speed-dating scene, but pre-computation of date-only values in a separate indexed column can save the day!

NULL values: party pooper alert

Handle potential nulls gracefully:

-- Better safe than sorry!
SELECT CAST(ISNULL(columnName, GETDATE()) AS DATE) FROM tableName


With NULL, always play defence!

Date querying etiquette

Follow the unwritten plain-text-odex:

• ANSI standard date formats (YYYY-MM-DD) — no room for ambiguity.
• Ditch BETWEEN for >= and < in date comparisons — why deal with time boundaries?
• Respect time zones and daylight saving shifts, or prepare for unexpected hiccups!