How to Get Current Date Without Time in SQL Server

In SQL Server, retrieving the current date without the time component is a common requirement, especially when working with date-based operations or reporting. This blog post will guide you through different methods to obtain the current date without the time portion in SQL Server.

Method 1: Using the CAST or CONVERT Function

One straightforward way to get the current date without the time is to use the CAST or CONVERT function to convert the current datetime to a date datatype. Here’s an example:

or

Both of these queries will return the current date without the time component.

Method 2: Using the DATEADD Function

Another approach is to subtract the time part from the current datetime using the DATEADD function. You can subtract the number of milliseconds that represent the time portion. Here’s how:

This query calculates the number of days between the ‘0’ date (1900-01-01) and the current date (GETDATE()), effectively removing the time portion.

Method 3: Using FORMAT Function (SQL Server 2012 and later)

If you are using SQL Server 2012 or later, you can use the FORMAT function to format the date as ‘yyyy-MM-dd,’ which effectively removes the time component:

Method 4: Using SQL Server Date Functions (SQL Server 2012 and later)

Starting from SQL Server 2012, you can use the DATEFROMPARTS function to construct a date without the time component:

Retrieving the current date without the time component in SQL Server is essential for various applications. Depending on your SQL Server version and personal preferences, you can choose one of the methods mentioned above to achieve this. Whether it’s for reporting or data manipulation, having a clean date without time simplifies working with date values in SQL Server. If you want to get updated, like the facebook page https://www.facebook.com/LearningBigDataAnalytics and stay connected.