MS SQL Server – How to get Date only from the datetime value?

To get the current date and time:

SELECT getdate();

And we have a datetime value: 2024-12-03 18:13:07.271

From the datetime value above, you want to extract the date value only. There are several ways to do that:

• Use CONVERT to VARCHAR:

CONVERT syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )  

In this case, date only, you we are gonna run this query:

SELECT CONVERT(VARCHAR(10), getdate(), 111);

It returns 2024/12/03 for my test.

The style we used just now is 111, which is yyyy/mm/dd. There are many other style you can choose from. Here are some common types:

Style	How it’s displayed
101	mm/dd/yyyy
102	yyyy.mm.dd
103	dd/mm/yyyy
104	dd.mm.yyyy
105	dd-mm-yyyy
110	mm-dd-yyyy
111	yyyy/mm/dd
106	dd mon yyyy
107	Mon dd, yyyy

Because each type generates a different length, so you should define the right varchar length then.

• You can also convert to date:

SELECT CONVERT(date, getdate());

It will return the current date value along with starting value for time. For example, the result for my case is:

Dec  3 2024 12:00:00:AM

For older version than SQL Server 2008, you should use this instead:

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

And it returns the same result.

• Use CAST

CAST syntax:

CAST ( expression AS data_type [ ( length ) ] )  

For the example above, you can use:

SELECT CAST(getdate() AS date);

Or you can cast it to varchar:

SELECT CAST(getdate() AS varchar(10));