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

Here’s your solution:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

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

gives me

2008-09-22 00:00:00.000

Pros:

• No varchar<->datetime conversions required
• No need to think about locale

How to get only the Date from a SQL Server DateTime datatype?

You can use the ‘date‘ data type to get only the date from a SQL server in SQLServer 2008 and beyond.

SQLServer 2008 now has a ‘date’ data type that contains only a date with no time component. Anyone using SQLServer 2008 and beyond can do the following:

SELECT CONVERT(date, GETDATE())

In other words,

If using SQL 2008 and above, this is the solution:

select cast(getdate() as date)

Answer #3:

Just do:

SELECT CAST(date_variable AS date)

or with with PostgreSQL:

SELECT date_variable::date

This is called typecasting btw!

Alternatively,

SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))

Answer #4:

If you need the result as a varchar, you should go through

SELECT CONVERT(DATE, GETDATE()) --2014-03-26
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) --2014/03/26

which is already mentioned above.

If you need result in date and time format, you should use any of the queries below

1. SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 111)) AS OnlyDate
   2014-03-26 00:00:00.000
2. SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 112)) AS OnlyDate
   2014-03-26 00:00:00.000
3. DECLARE @OnlyDate DATETIME SET @OnlyDate = DATEDIFF(DD, 0, GETDATE()) SELECT @OnlyDate AS OnlyDate
   2014-03-26 00:00:00.000

Answer #5:

Date:

SELECT CONVERT(date, GETDATE())
SELECT CAST(GETDATE() as date)

Time:

SELECT CONVERT(time , GETDATE() , 114)
SELECT CAST(GETDATE() as time)

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

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

Answer #7:

DATEADD and DATEDIFF are better than CONVERTing to varchar. Both queries have the same execution plan, but execution plans are primarily about data access strategies and do not always reveal implicit costs involved in the CPU time taken to perform all the pieces. If both queries are run against a table with millions of rows, the CPU time using DateDiff can be close to 1/3rd of the Convert CPU time!

To see execution plans for queries:

set showplan_text on
GO 

Both DATEADD and DATEDIFF will execute a CONVERT_IMPLICIT.

Although the CONVERT solution is simpler and easier to read for some, it is slower. There is no need to cast back to DateTime (this is implicitly done by the server). There is also no real need in the DateDiff method for DateAdd afterward as the integer result will also be implicitly converted back to DateTime.

---

SELECT CONVERT(varchar, MyDate, 101) FROM DatesTable

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT(varchar(30),[TEST].[dbo].[DatesTable].[MyDate],101)))
       |--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))

---

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, MyDate)) FROM DatesTable

  |--Compute Scalar(DEFINE:([Expr1004]=dateadd(day,(0),CONVERT_IMPLICIT(datetime,datediff(day,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[TEST].[dbo].[DatesTable].[MyDate],0)),0))))
       |--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))

Using FLOOR() as @digi suggested has performance closer to DateDiff, but is not recommended as casting the DateTime data type to float and back does not always yield the original value.

Remember guys: Don’t believe anyone. Look at the performance statistics, and test it yourself!

Be careful when you’re testing your results. Selecting many rows to the client will hide the performance difference because it takes longer to send the rows over the network than it does to perform the calculations. So make sure that the work for all the rows is done by the server but there is no row set sent to the client.

There seems to be confusion for some people about when cache optimization affects queries. Running two queries in the same batch or in separate batches has no effect on caching. So you can either expire the cache manually or simply run the queries back and forth multiple times. Any optimization for query #2 would also affect any subsequent queries, so throw out execution #1 if you like.

If you are using SQL Server 2012 or above versions,

Use Format() function to return only the Date from a SQL Server DateTime datatype.

There are already multiple answers and formatting types for SQL server. But most of the methods are somewhat ambiguous and it would be difficult for you to remember the numbers for format type or functions with respect to Specific Date Format. That’s why in next versions of SQL server there is better option.

FORMAT ( value, format [, culture ] )

Culture option is very useful, as you can specify date as per your viewers.

You have to remember d (for small patterns) and D (for long patterns).

1.”d” – Short date pattern.

2009-06-15T13:45:30 -> 6/15/2009 (en-US)
2009-06-15T13:45:30 -> 15/06/2009 (fr-FR)
2009-06-15T13:45:30 -> 2009/06/15 (ja-JP)

2.”D” – Long date pattern.

2009-06-15T13:45:30 -> Monday, June 15, 2009 (en-US)
2009-06-15T13:45:30 -> 15 июня 2009 г. (ru-RU)
2009-06-15T13:45:30 -> Montag, 15. Juni 2009 (de-DE)

More examples in query.

DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'; 

SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';

US English Result Great Britain English Result  German Result Simplified Chinese (PRC) Result
----------------  ----------------------------- ------------- -------------------------------------
10/1/2011         01/10/2011                    01.10.2011    2011/10/1

US English Result            Great Britain English Result  German Result                    Chinese (Simplified PRC) Result
---------------------------- ----------------------------- -----------------------------  ---------------------------------------
Saturday, October 01, 2011   01 October 2011               Samstag, 1. Oktober 2011        2011年10月1日

Hope you learned something from this post. The primary source of this article is StackOverflow.

Follow Programming Articles for more!