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: 2018-09-01 11:50:05.627

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

1. 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 2018/09/01 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.

2. 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:

Sep  1 2018 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.

3. 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));

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple databases including SQL Server, MySQL, PostgreSQL, SQLite, etc. And it’s free to use for as long as you need it to.