How to return only the Date from a SQL Server DateTime datatype.

This code example uses SQL Server 2014 Standard

Sometimes we need to only return the SQL date part of a column from a SQL database table. Since SQL Server 2008 you now have a date data type. But for this post, I will not use that, but instead, use the DateTime data type.

The table used for this example can be found in a previous blog here.

To start let's modify the table and add a new DateTime column, the code for that is below:

ALTER TABLE [dbo].[PersonNameAndAddress]
ADD DateAdded DateTime DEFAULT(Getdate())

All we are doing here is adding a new column and giving it a default value of GetDate().

Running the above SQL statement will add a new column as the image below shows.

How to return only the Date from a SQL Server DateTime datatype

So now let's create an SQL query that will return data including the date data types we are after, for this example I will return the full DateTime, date only and time only data type.

--Simple select statement returning the full date
select [FirstName],[LastName],[DateAdded] FROM [dbo].[PersonNameAndAddress]

--Simple select statement returning the just the date
select [FirstName],[LastName],Convert(nvarchar,[DateAdded],106) AS DateAdded FROM [dbo].[PersonNameAndAddress]

--Simple select statement returning the just the time
select [FirstName],[LastName],Convert(nvarchar,[DateAdded],8) AS TimeAdded FROM [dbo].[PersonNameAndAddress]

The above will return the following date and time:

Different date formats

If you want to see all the date and time formats SQL Server has, then you can run the SQL query below.

IF OBJECT_ID(N'tempdb..#dbDateFormats') IS NOT NULL
BEGIN
DROP TABLE #dbDateFormats
END
GO
DECLARE @currentDateTime DateTime
SET @currentDateTime = GetDate()
DECLARE @counter INT = 0
DECLARE @dateTime DATETIME = @currentDateTime

CREATE TABLE #dbDateFormats (dateNumber int, dateFormatOutput nvarchar(40))

WHILE (@counter <= 200 )
BEGIN
   BEGIN TRY
      INSERT INTO #dbDateFormats
      SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@dateTime, @counter) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter >= 200
      BEGIN
         BREAK
      END
   END CATCH
END

SELECT * FROM #dbDateFormats

Running the above with SQL Server 2019 returns the following:

Date NumberDate Format Output
0Oct 27 2021 4:48PM
110/27/21
221.10.27
327/10/21
427.10.21
527-10-21
627 Oct 21
7Oct 27, 21
816:48:15
9Oct 27 2021 4:48:15:060PM
1010-27-21
1121/10/27
12211027
1327 Oct 2021 16:48:15:060
1416:48:15:060
202021-10-27 16:48:15
212021-10-27 16:48:15.060
2210/27/21 4:48:15 PM
232021-10-27
2416:48:15
252021-10-27 16:48:15.060
262021-27-10 16:48:15.060
2710-27-2021 16:48:15.060
2810-2021-27 16:48:15.060
2927-10-2021 16:48:15.060
3027-2021-10 16:48:15.060
312021-27-10
3210-27-2021
3310-2021-27
3427-10-2021
3527-2021-10
1002Oct 27 2021 4:48PM
10110/27/2021
1022021.10.27
10327/10/2021
10427.10.2021
10527-10-2021
10627 Oct 2021
107Oct 27, 2021
10816:48:15
109Oct 27 2021 4:48:15:060PM
11010-27-2021
1112021/10/27
11220211027
11327 Oct 2021 16:48:15:060
11416:48:15:060
1202021-10-27 16:48:15
1212021-10-27 16:48:15.060
1262021-10-27T16:48:15.060
1272021-10-27T16:48:15.060
13021 ربيع الاول 1443 4:47:39:01
13121/03/1443 4:48:15:060PM

Running the above with SQL Server 2014 returns the following:

Date NumberDate Format Output
0Oct 27 2021 4:47PM
110/27/21
221.10.27
327/10/21
427.10.21
527-10-21
627 Oct 21
7Oct 27, 21
816:47:39
9Oct 27 2021 4:47:39:013PM
1010-27-21
1121/10/27
12211027
1327 Oct 2021 16:47:39:013
1416:47:39:013
202021-10-27 16:47:39
212021-10-27 16:47:39.013
2210/27/21 4:47:39 PM
232021-10-27
2416:47:39
2522021-10-27 16:47:39.013
100Oct 27 2021 4:47PM
10110/27/2021
1022021.10.27
10327/10/2021
10427.10.2021
10527-10-2021
10627 Oct 2021
107Oct 27, 2021
10816:47:39
109Oct 27 2021 4:47:39:013PM
11010-27-2021
1112021/10/27
11220211027
11327 Oct 2021 16:47:39:013
11416:47:39:013
1202021-10-27 16:47:39
1212021-10-27 16:47:39.013
1262021-10-27T16:47:39.013
1272021-10-27T16:47:39.013
13021 ربيع الاول 1443 4:47:39:01
13121/03/1443 4:47:39:013PM

I hope you found this blog helpful and thanks for reading.