Get the Current Date in SQL Server

Hello Dev, in this article, we will be discussing how to get the current date in SQL Server. As you may know, working with date and time values is important in any database system. There are times when you may need to retrieve the current date from the system. We will be looking at various ways to get the current date in SQL Server. Let’s dive in!

Using the GETDATE() Function

The GETDATE() function is a built-in function in SQL Server that returns the current date and time. You can use this function to get the current date in SQL Server. Here is an example:

SELECT GETDATE()

2022-06-15 17:09:39.437

The GETDATE() function returns the current date and time in the format ‘yyyy-mm-dd hh:mm:ss.mmm’. If you only want the current date without the time, you can use the CONVERT() function to extract the date part. Here is an example:

SELECT CONVERT(date, GETDATE())

2022-06-15

FAQs

What is the syntax of the GETDATE() function?

The syntax of the GETDATE() function is:

GETDATE()

What is the data type returned by the GETDATE() function?

The GETDATE() function returns a datetime data type.

Using the SYSDATETIME() Function

The SYSDATETIME() function is similar to the GETDATE() function, but it returns a more precise value including the fractional seconds. Here is an example:

SELECT SYSDATETIME()

2022-06-15 17:09:39.4377433

You can use the CONVERT() function to extract the date or time part. Here is an example:

SELECT CONVERT(date, SYSDATETIME())

2022-06-15

FAQs

What is the syntax of the SYSDATETIME() function?

The syntax of the SYSDATETIME() function is:

SYSDATETIME()

What is the data type returned by the SYSDATETIME() function?

The SYSDATETIME() function returns a datetime2 data type.

Using the CURRENT_TIMESTAMP Function

The CURRENT_TIMESTAMP function is another built-in function in SQL Server that returns the current date and time. Here is an example:

SELECT CURRENT_TIMESTAMP

2022-06-15 17:09:39.437

You can use the CONVERT() function to extract the date or time part. Here is an example:

SELECT CONVERT(date, CURRENT_TIMESTAMP)

2022-06-15

FAQs

What is the syntax of the CURRENT_TIMESTAMP function?

The syntax of the CURRENT_TIMESTAMP function is:

CURRENT_TIMESTAMP

What is the data type returned by the CURRENT_TIMESTAMP function?

The CURRENT_TIMESTAMP function returns a datetime data type.

Using the @@DATEFIRST System Variable

The @@DATEFIRST system variable specifies the first day of the week. You can use this variable to get the current date in SQL Server. Here is an example:

DECLARE @today datetimeSET @today = DATEADD(day, (@@DATEFIRST - DATEPART(dw, GETDATE())), GETDATE())SELECT @today

2022-06-13 17:09:39.437

This code calculates the current date by subtracting the day of the week of the current date from the @@DATEFIRST value and adding the result to the current date. This ensures that the current date is based on the first day of the week specified by the @@DATEFIRST value.

FAQs

What is the syntax of the @@DATEFIRST system variable?

The syntax of the @@DATEFIRST system variable is:

@@DATEFIRST

What is the default value of the @@DATEFIRST system variable?

The default value of the @@DATEFIRST system variable is 7, which represents Sunday.

Using the DATEFROMPARTS Function

The DATEFROMPARTS function is a new function introduced in SQL Server 2012 that allows you to create a date from individual year, month, and day components. Here is an example:

SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()))

2022-06-15

This code extracts the year, month, and day components of the current date using the YEAR(), MONTH(), and DAY() functions and passes them as arguments to the DATEFROMPARTS function.

FAQs

What is the syntax of the DATEFROMPARTS function?

The syntax of the DATEFROMPARTS function is:

DATEFROMPARTS ( year, month, day )

What is the data type returned by the DATEFROMPARTS function?

The DATEFROMPARTS function returns a date data type.

Using the CURRENT_DATE Function

The CURRENT_DATE function is a new function introduced in SQL Server 2019 that returns the current date. Here is an example:

SELECT CURRENT_DATE

2022-06-15

This function returns the current date without the time component.

FAQs

What is the syntax of the CURRENT_DATE function?

The syntax of the CURRENT_DATE function is:

CURRENT_DATE

What is the data type returned by the CURRENT_DATE function?

The CURRENT_DATE function returns a date data type.

Conclusion

In this article, we have looked at various ways to get the current date in SQL Server. We have covered built-in functions such as GETDATE(), SYSDATETIME(), and CURRENT_TIMESTAMP, as well as system variables and new functions introduced in SQL Server 2012 and 2019. We hope that this has helped you understand how to retrieve the current date in SQL Server. If you have any questions, please feel free to ask in the comments section below. Thank you for reading!