To get current date uses SQL Server date function and GETDATE() function.
The GETDATE() function in SQL Server returns the current date and time as a datetime value. This value is based on the system's clock and does not require any input parameters.

Syntax

GETDATE();
GETUTCDATE();
SYSDATETIME();
SYSUTCDATETIME();
CURRENT_TIMESTAMP;
SYSDATETIMEOFFSET();

Examples

Here is an example of how to use the GETDATE() function in a SELECT statement:

SELECT GETDATE() AS 'Current Date and Time';

For example, to get the current date only and not the time, you can use the CONVERT() function to format the date:

SELECT CONVERT(date, GETDATE()) AS 'Current Date';

Another way to get the current date is by using the SYSDATETIME() function, it returns the current date and time as a datetime2(7) data type, which has a higher precision than the datetime data type.

SELECT SYSDATETIME() as 'Current Date and Time';

You can also use CURRENT_TIMESTAMP to get the current date and time in SQL Server.

SELECT CURRENT_TIMESTAMP as 'Current Date and Time';

Other examples

SELECT GETDATE();
SELECT GETUTCDATE();
SELECT SYSDATETIME();
SELECT SYSUTCDATETIME();
SELECT CURRENT_TIMESTAMP;
SELECT SYSDATETIMEOFFSET();

It's important to note that depending on the version of SQL Server you are using, the function and the format might have slight variations.