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.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS

TablePlus for MS SQL Server