SQL Server Get Current Date

As a senior database developer working with SQL Server, I’ve found that retrieving the current date and time is one of your most fundamental operations. In this comprehensive article, I’ll walk you through various methods to get the current date in SQL Server and share best practices based on real-world examples.

Table of Contents

SQL Server provides several built-in functions to retrieve the current date and time. Each serves a specific purpose and has unique characteristics that make it suitable for different scenarios.

Using GETDATE()

The most commonly used function to retrieve the current date and time in SQL Server is GETDATE(). This function returns the current database system timestamp as a datetime value.

SELECT GETDATE() AS CurrentDateTime;

After executing the above query, I got the expected output, as shown in the below screenshot.

Using CURRENT_TIMESTAMP

If you’re working in an environment where code portability is essential, CURRENT_TIMESTAMP is your best option as it follows the ANSI SQL standard:

SELECT CURRENT_TIMESTAMP AS CurrentDateTime;

After executing the above query, I got the expected output, which is shown in the screenshot below.

Getting Just the Date (Without Time)

Sometimes, you only need the date portion without the time. Here are methods I’ve successfully implemented for my clients:

Method 1: Using CONVERT ()

To get the current date, we can use the query below with CONVERT() and GETDATE().

SELECT CONVERT(date, GETDATE()) AS CurrentDate;

After executing the above query, I got the expected output, as shown in the screenshot below.

sql server get current date without time

Method 2: Using CAST ()

We can also use the query below with CAST() and GETDATE() to get the current date.

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

After executing the above query, I got the expected output, as shown in the screenshot below.

how to get current date without time in sql

Method 3: Using DATEADD and DATEDIFF

We can use the DATEADD () and DATEDIFF (). This method effectively truncates the time portion and only provides the current date. We can use the query below for this purpose.

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS CurrentDate;

After executing the above query, I got the expected output, as shown in the below screenshot.

how to get current date in sql server without time

Formatting Dates

These methods are effortless when presenting dates to users or storing them in specific formats.

Common Date Formats

Format	Code	Output
MM/DD/YYYY	101	08/15/2023
YYYY-MM-DD	120	2023-08-15
Mon DD YYYY	107	Aug 15 2023
YYYY/MM/DD	111	2023/08/15

Example

SELECT 
    CONVERT(varchar, GETDATE(), 101) AS USDateFormat,
    CONVERT(varchar, GETDATE(), 120) AS ISODateFormat,
    CONVERT(varchar, GETDATE(), 107) AS MonthDayYearFormat;

After executing the above command, I got the expected output, as shown in the below screenshot.

how to get current date in sql without time

Best Practices for Working with Dates in SQL Server

Based on my experience optimizing database performance, here are my recommendations:

Use appropriate data types: Use date When you only need the date, saving storage space and improving query performance
Be mindful of indexes: Avoid functions on indexed date columns in WHERE clauses
Consider time zones: Always store dates in UTC and convert to local time zones at the application level.

Conclusion

Using date and time functions will significantly enhance your database development capabilities. From the standard GETDATE() to more specialized functions like SYSDATETIMEOFFSET(), SQL Server provides you with this option to handle this.