How to extract date from date in sql server step by step guide: Master CAST, CONVERT, and DATEPART for clean dates

Table of Contents

Extract the date portion from a datetime in SQL Server by using CASTyourColumn AS DATE or CONVERTDATE, yourColumn. In this guide, you’ll learn a straightforward, step-by-step approach to pulling out the date, handling different data types, and avoiding common pitfalls. We’ll cover practical examples, best practices, and performance tips. You’ll also see how to format dates for display, filter by date ranges, and optimize queries that rely on date extraction. Here’s what you’ll get:
– A clear step-by-step process you can apply today
– Real-world examples for common data types DATE, DATETIME, DATETIME2, SMALLDATETIME
– Quick-reference tables comparing date/time types and their storage
– Pitfalls to avoid like index usage when filtering by date
– Tips for formatting, time zones, and performance
Useful URLs and Resources:
– Microsoft Docs – docs.microsoft.com
– SQL Server Central – sqlservercentral.com
– Stack Overflow – stackoverflow.com
– Wikipedia – en.wikipedia.org/wiki/Date_and_time

Introduction to date extraction in SQL Server

When you’re dealing with dates and timestamps in SQL Server, you often want just the date part year-month-day without the time component. This is especially true for reporting, daily aggregations, or when you’re targeting a specific day in a large dataset. The two most common ways to extract the date part are:

– CASTcol AS DATE
– CONVERTDATE, col

This section walks you through why these methods matter, the exact syntax, and how they behave across different date/time data types.

Why this matters for performance and accuracy
– Using DATE alone saves storage and is faster for comparisons and grouping.
– Casting or converting to DATE strips away time, which is essential for daily aggregations.
– Be mindful of time zones if your data stores time in UTC or with offset. you may need to convert before extracting the date.

Understanding date-related data types in SQL Server

Here’s a quick, practical overview of the main date/time types you’ll encounter, with a focus on how date extraction works for each:

– DATE: Stores a date only range: 0001-01-01 to 9999-12-31. Uses 3 bytes. Perfect for birthdates, anniversaries, or any date-only value.
– TIMEp: Stores time of day with optional precision p 0-7. Range 00:00:00.0000000 to 23:59:59.9999999.
– DATETIME: Stores date and time, with precision about 3.33 milliseconds. Range 1753-01-01 to 9999-12-31. Uses 8 bytes.
– SMALLDATETIME: Stores date and time with lower precision minutes. Range 1900-01-01 to 2079-06-06. Uses 4 bytes.
– DATETIME2p: Stores date and time with user-defined precision p 0-7. Range 0001-01-01 to 9999-12-31. Uses 6-8 bytes depending on precision.
– DATETIMEOFFSETp: Like DATETIME2 with an offset preserved for time zone awareness. Range 0001-01-01 to 9999-12-31 with offsets from -14:00 to +14:00.

Key takeaway: If you only need the date portion, DATE is the simplest and most efficient type to work with going forward.

Step-by-step guide: how to extract date from a date column

Follow these steps to reliably extract the date portion from a datetime-like column and keep your queries fast and readable.

Step 1: Identify the date column
– Locate the column in your table that contains date/time data e.g., order_date, event_timestamp.
– Determine its data type DATE, DATETIME, DATETIME2, SMALLDATETIME. This matters for how you’ll handle formatting and indexing.

Step 2: Basic date extraction with CAST
– Use CAST to strip the time portion and return a DATE value.
– Example:
SELECT CASTorder_timestamp AS DATE AS order_date_only
FROM orders.

Step 3: Basic date extraction with CONVERT
– CONVERT offers a similar approach and is often used when you also need style codes for string output.
SELECT CONVERTDATE, event_time AS event_date
FROM events.

Step 4: When to prefer CAST vs CONVERT
– CAST is simpler and often clearer. CONVERT is helpful if you need to apply a style when converting to a string e.g., for display or when you’re working with legacy code.
– Examples:
SELECT CASTshipment_date AS DATE AS shipment_date
FROM shipments.
SELECT CONVERTDATE, shipment_time AS shipment_date

Step 5: Grouping and filtering by date without hurting indexes
– For grouping by date, cast in the SELECT and GROUP BY, but be mindful of index usage.
– For filtering by a specific date range on large tables, avoid applying a function directly to a column in the WHERE clause if it blocks index seeks.
– Good approach for filtering a range on a datetime column:
SELECT *
FROM logs
WHERE log_time >= ‘2026-01-01’ AND log_time < ‘2026-01-02’.
– If you must extract date for filtering, consider using an indexed computed column or a persisted computed column:
ALTER TABLE logs ADD log_date AS CASTlog_time AS DATE PERSISTED.
CREATE INDEX IX_logs_log_date ON logs log_date.
— Then:
WHERE log_date = ‘2026-01-01’.

Step 6: Handling NULLs and data quality
– If your date/datetime column can be NULL, always consider how that affects your downstream logic.
SELECT COALESCECASTevent_time AS DATE, ‘1900-01-01’ AS event_date
– Better yet, handle NULLs in your application logic and keep the database clean with constraints where appropriate.

Step 7: Working with DATETIME2 and precision
– When you’re extracting the date from a DATETIME2 column, CAST or CONVERT to DATE behaves the same way, but you’ll preserve precise storage in the underlying column.
SELECT CASTorder_time AS DATE AS order_date
FROM orders_datetime2.

Step 8: Time zone considerations
– If your data uses time zones, convert to the desired zone before extracting the date.
– Example simplified:
SELECT CASTorder_utc_time AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Pacific Standard Time’ AS DATE AS order_pst_date

Step 9: Formatting dates for display
– If you need a string representation, FORMAT is flexible but slower. prefer CONVERT with style codes on strings when possible.
SELECT CONVERTVARCHAR10, CASTorder_time AS DATE, 120 AS date_iso
SELECT FORMATorder_time, ‘yyyy-MM-dd’ AS date_string

Step 10: Best practices and hygiene
– Prefer DATE type for date-only fields going forward.
– Keep time information in DATETIME23 or higher if you need precision, and strip time only when you’re ready to display or group by date.
– Index date-related columns. consider computed columns if you frequently group or filter by the date portion.

Practical examples and quick references

Sample data and common scenarios to keep handy:

– Scenario: Get the date part from a DATETIME column for daily sales totals
– SQL:
SELECT CASTsale_time AS DATE AS sale_date, SUMamount AS total_sales
FROM sales
GROUP BY CASTsale_time AS DATE
ORDER BY sale_date.

– Scenario: Filter records from a date only perspective without losing index efficiency
SELECT *
FROM events
WHERE event_time >= ‘2026-02-01’ AND event_time < ‘2026-02-02’.

– Scenario: Ensure no time part when comparing dates across systems
FROM users
WHERE CASTbirthdate AS DATE = ‘1990-05-21’.

– Scenario: Convert to a date string for reporting
SELECT CASTorder_date AS VARCHAR10 AS order_date_str
FROM orders.

– Scenario: When storing dates, switch to DATE to save space
ALTER TABLE customers ADD signup_date DATE.

Table: date/time types at a glance storage and use cases

| Data Type | Typical Use Case | Storage | Date Range |
|—————|————————————–|———|——————————–|
| DATE | Date-only values | 3 bytes | 0001-01-01 to 9999-12-31 |
| TIMEp | Time of day only | 5-16 bytes depending on p | 00:00:00.0000000 to 23:59:59.9999999 |
| DATETIME | Date and time, legacy support | 8 bytes | 1753-01-01 to 9999-12-31 |
| SMALLDATETIME | Date and time with minute precision | 4 bytes | 1900-01-01 to 2079-06-06 |
| DATETIME2p | Precise date and time, modern use | 6-8 bytes | 0001-01-01 to 9999-12-31 |
| DATETIMEOFFSETp | Date/time with time zone offset | 8-9 bytes | 0001-01-01 to 9999-12-31 with offset -14:00 to +14:00 |

Tip: For new designs, prefer DATE and DATETIME2 with appropriate precision for better performance and future-proofing.

Best practices for date extraction and querying

– Use DATE for date-only logic and filtering whenever possible.
– Avoid applying functions to a column in a WHERE clause if you can restructure the predicate to preserve index seeks.
– Consider adding a persisted computed column for the date portion if you frequently filter or group by the date, then index that column.
– When working with time zones, normalize to a common zone typically UTC before extracting the date portion for reporting.
– Use appropriate string formats only for display. keep data in date/time types for calculations and comparisons.
– Document your date conventions in your data dictionary to prevent confusion between date-only fields and date-time fields.

Real-world scenarios and performance tips

– Aggregation by date on large datasets
– Use a persisted computed column for the date portion and index it to speed up GROUP BY operations.
– Example:
ALTER TABLE orders ADD order_date_only AS CASTorder_time AS DATE PERSISTED.
CREATE INDEX IX_orders_order_date_only ON orders order_date_only.
– Filtering by a date range for dashboards
– Prefer range-based filters on the datetime column itself to keep index usage:
WHERE event_time >= @startDate AND event_time < DATEADDDAY, 1, @startDate.
– Handling historical data
– If you have mixed data types, convert on the read path and store consistently for outputs:
SELECT CASTevent_time AS DATE AS event_date, COUNT FROM events GROUP BY CASTevent_time AS DATE.

Troubleshooting: common mistakes to avoid

– Mistake: Using WHERE CASTdate_col AS DATE = ‘2026-02-01′ on large tables
– Why it’s bad: Casting on the column can prevent index usage, slowing down queries.
– Fix: Use a range predicate on the original column, or create a persisted date column as a filter key.
– Mistake: Relying on string comparison for dates
– Why it’s bad: It’s locale-sensitive and error-prone. always use proper date types for logic.
– Fix: Keep dates as DATE or DATETIME/DATETIME2 as long as possible. cast only when needed for display.
– Mistake: Mixing time zones without a standard
– Why it’s bad: Offsets can cause off-by-one-day errors.
– Fix: Normalize to UTC, then extract the date.

Frequently Asked Questions

# 1. How do I extract only the date from a DATETIME column?
You can use CASTyourColumn AS DATE or CONVERTDATE, yourColumn. For example:
SELECT CASTorder_time AS DATE AS order_date FROM orders.

# 2. Can I extract the date from a string that looks like a date?
Yes, but you should first parse the string into a date type, then extract the date. Example:
SELECT CAST’2026-03-15 12:34:56′ AS DATETIME AS dt, CASTCAST’2026-03-15 12:34:56’ AS DATETIME AS DATE AS only_date.

# 3. What’s the difference between DATE and DATETIME2 for new projects?
DATE stores only the date portion, uses less storage and is ideal for dates without time. DATETIME2 stores date and time with higher precision and a larger range. it’s generally preferred when you need precise time data or want to future-proof with flexible precision.

# 4. How do I extract the date from a DATETIME2 column?
Use the same approach as with DATETIME: CASTdatetime2_column AS DATE or CONVERTDATE, datetime2_column.

# 5. How can I format a date for display in a specific format?
Use FORMAT easy but slower or CONVERT to a string with a style code. Examples:
SELECT FORMATorder_time, ‘yyyy-MM-dd’ AS date_str.
SELECT CONVERTVARCHAR10, CASTorder_time AS DATE, 120 AS date_str_iso.

# 6. How do I group by date when I have timestamps?
Cast to date in both SELECT and GROUP BY:
SELECT CASTorder_time AS DATE AS order_date, COUNT FROM orders GROUP BY CASTorder_time AS DATE.

# 7. What should I do to optimize queries that extract date from a large table?
Create a persisted computed column for the date portion, then index that column. This helps preserve index seeks while keeping the date logic intact.

# 8. Is there a performance difference between CAST and CONVERT for date extraction?
Performance is generally similar for both when just extracting a date. CAST is more concise. CONVERT is useful if you later need string styles or compatibility with older code.

# 9. How do I handle time zones when extracting dates?
If your data uses UTC or offsets, convert to the target time zone first using AT TIME ZONE and then extract the date.

# 10. Can I extract the date portion during data loading or ETL?
Yes. Normalize to the date during ETL, store as DATE, and only convert to strings for reports.

# 11. How do I replace NULL dates with a default date?
Use COALESCE around the extraction:
SELECT COALESCECASTorder_time AS DATE, ‘1900-01-01’ AS order_date FROM orders.

# 12. When should I convert dates to strings?
Only for display purposes in reports or UI. Keep the underlying data as a date type for calculations and filtering.

This guide is designed to help you reliably extract the date portion from any date/time value in SQL Server, with practical steps, real-world examples, and best practices you can apply immediately. If you want to dive deeper, you can check the included resources for official documentation and community insights.