Reputation: 34878

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

SELECT GETDATE()

Returns: 2008-09-22 15:24:13.790

I want that date part without the time part: 2008-09-22 00:00:00.000

How can I get that?

Upvotes: 2214

Answers (30)

Irina Danovich

Reputation: 93

where datediff(day, [TableColumnName], '2024-03-26') = 0

Upvotes: -1

aku

Reputation: 124034

NOTE: This answer returns the original DATETIME or DATETIME2 type. For an expression that returns a true DATE type (SQL Server 2008 and later), see BenR's answer below.

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

Upvotes: 2870

Hongjin Zhu

Reputation: 25

SELECT CONVERT(varchar(100), GETDATE(), 102); --2023.02.15
SELECT CONVERT(varchar(100), GETDATE(), 23);  --2023-02-15

you can fllow this url to find some other format

example:

https://www.cnblogs.com/wintuzi/p/16164124.html

hope i can help you

Upvotes: 1

Zhorov

Reputation: 29993

Starting from SQL Server 2022 (16.x), another option is DATETRUNC() function using day as value of datepart parameter:

SELECT DATETRUNC(day, GETDATE());

Upvotes: 5

Ricardo C

Reputation: 2244

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.

Here is full test script and performance results that prove DateDiff is substantially faster than converting to varchar.

Upvotes: 89

John Sonnino

Reputation: 579

Just do:

SELECT CAST(date_variable AS date)

or with with PostgreSQL:

SELECT date_variable::date

This is called typecasting btw!

Upvotes: 36

yusuf hayırsever

Reputation: 701

Syntax:

SELECT CONVERT (data_type(length)),Date, DateFormatCode)

Ex:

Select CONVERT(varchar,GETDATE(),1) as [MM/DD/YY]
Select CONVERT(varchar,GETDATE(),2) as [YY.MM.DD]

all dateformatcodes about Date:

DateFormatCode  Format
1       [MM/DD/YY]
2       [YY.MM.DD]
3       [DD/MM/YY]
4       [DD.MM.YY]
5       [DD-MM-YY]
6       [DD MMM YY]
7       [MMM DD,YY]
10      [MM-DD-YY]
11      [YY/MM/DD]
12      [YYMMDD]
23      [yyyy-mm-dd]
101     [MM/DD/YYYY]
102     [YYYY.MM.DD]
103     [DD/MM/YYYY]
104     [DD/MM/YYYY]
105     [DD/MM/YYYY]
106     [DD MMM YYYY]
107     [MMM DD,YYYY]
110     [MM-DD-YYYY]
111     [YYYY/MM/DD]
112     [YYYYMMDD]

Upvotes: 11

Christopher Warrington

Reputation: 767

As there has been many changes since this question had answers, I wanted to provide a new way to get the requested result. There are two ways to parse DATETIME data. First, to get the date as this question asks:

DATEVALUE([TableColumnName])

Second, to get the time from the value:

TIMEVALUE([TableColumnName])

Example:

Table: Customers

Column: CreationDate as DateTime

[Customers].[CreationDate]: 2/7/2020 09:50:00

DATEVALUE([Customers].[CreationDate]) '--> Output: 2/7/2020
TIMEVALUE([Customers].[CreationDate]) '--> Output: 09:50:00

I hope that this helps as I was searching for a while and found many answers as seen in this question and none of those worked. IE CAST and CONVERT.

Happy Coding!

Upvotes: -1

Mohammad Neamul Islam

Reputation: 283

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

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

Upvotes: 9

Stephon Johns

Reputation: 361

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

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 111)) AS OnlyDate

2014-03-26 00:00:00.000

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 112)) AS OnlyDate

2014-03-26 00:00:00.000

DECLARE  @OnlyDate DATETIME
SET @OnlyDate = DATEDIFF(DD, 0, GETDATE())
SELECT @OnlyDate AS OnlyDate

2014-03-26 00:00:00.000

Upvotes: 26

mokh223

Reputation: 624

select convert(getdate() as date)

select CONVERT(datetime,CONVERT(date, getdate()))

Upvotes: -1

ChrisM

Reputation: 505

If you want the date to show 2008-09-22 00:00:00.000

then you can round it using

SELECT CONVERT(datetime, (ROUND(convert(float, getdate()-.5),0)))

This will show the date in the format in the question

Upvotes: 3

Jithin Joy

Reputation: 146

The easiest way would be to use: SELECT DATE(GETDATE())

Upvotes: -2

Aubrey Love

Reputation: 1034

Wow, let me count the ways you can do this. (no pun intended)

In order to get the results you want in this format specifically:

2008-09-22

Here are a few options.

SELECT CAST(GETDATE() AS DATE) AS 'Date1'
SELECT Date2  = CONVERT(DATE, GETDATE())
SELECT CONVERT(DATE, GETDATE()) AS 'Date3'
SELECT CONVERT(CHAR(10), GETDATE(), 121) AS 'Date4'
SELECT CONVERT(CHAR(10), GETDATE(), 126) AS 'Date5'
SELECT CONVERT(CHAR(10), GETDATE(), 127) AS 'Date6'

So, I would suggest picking one you are comfortable with and using that method across the board in all your tables.

All these options return the date in the exact same format. Why does SQL Server have such redundancy?

I have no idea, but they do. Maybe somebody smarter than me can answer that question.

Hope this helps someone.

Upvotes: 1

ankit soni

Reputation: 35

you can use like below for different different type of output for date only

SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 103)) -----dd/mm/yyyy
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))------mm/dd/yyyy
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 102))

Upvotes: 1

karthik kasubha

Reputation: 434

select cast(createddate as date) as derivedate from table

createdate is your datetime column , this works for sqlserver

Upvotes: 3

CAGDAS AYDIN

Reputation: 61

My Style

      select Convert(smalldatetime,Convert(int,Convert(float,getdate())))

Upvotes: 2

Nescio

Reputation: 28443

Try this:

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

The above statement converts your current format to YYYY/MM/DD, please refer to this link to choose your preferable format.

Upvotes: 65

Spider

Reputation: 524

My common approach to get date without the time part..

 SELECT CONVERT(VARCHAR(MAX),GETDATE(),103)

 SELECT CAST(GETDATE() AS DATE)

Upvotes: 3

Amar Srivastava

Reputation: 373

Simply you can do this way:

SELECT CONVERT(date, getdate())
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Outputs as:

2008-09-22 00:00:00.000

Or simply do like this:

SELECT CONVERT (DATE, GETDATE()) 'Date Part Only'

Result:

Date Part Only
--------------
2013-07-14

Upvotes: 9

r-magalhaes

Reputation: 458

On SQL Server 2000

CAST(
(
    STR( YEAR( GETDATE() ) ) + '/' +
    STR( MONTH( GETDATE() ) ) + '/' +
    STR( DAY( GETDATE() ) )
)
AS DATETIME)

Upvotes: 5

Art Schmidt

Reputation: 141

If you are assigning the results to a column or variable, give it the DATE type, and the conversion is implicit.

DECLARE @Date DATE = GETDATE()   

SELECT @Date   --> 2017-05-03

Upvotes: 12

Rushda

Reputation: 197

SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),103) --21/09/2011

SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),101) --09/21/2011

SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),111) --2011/09/21

SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),107) --Sep 21, 2011

Upvotes: 18

Somnath Muluk

Reputation: 57786

If you are using SQL Server 2012 or above versions,

Use Format() function.

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日

If you want more formats, you can go to:

Upvotes: 23

xbb

Reputation: 2163

Starting from SQL SERVER 2012, you can do this:

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000')

Upvotes: 8

Kris Khairallah

Reputation: 1582

Date:

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

Time:

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

Upvotes: 13

BenR

Reputation: 12326

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

SELECT CONVERT(date, GETDATE())

Upvotes: 1007

Krishnraj Rana

Reputation: 6656

Okay, Though I'm bit late :), Here is the another solution.

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) as DATETIME)

Result

2008-09-22 00:00:00.000

And if you are using SQL Server 2012 and higher then you can use FORMAT() function like this -

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd')

Upvotes: 8

Shyam Bhimani

Reputation: 1270

You can simply use the code below to get only the date part and avoid the time part in SQL:

SELECT SYSDATE TODAY FROM DUAL;

Upvotes: -1

etni

Reputation: 81

DECLARE @yourdate DATETIME = '11/1/2014 12:25pm'    
SELECT CONVERT(DATE, @yourdate)

Upvotes: 8

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

Answers (30)

If you are using SQL Server 2012 or above versions,

1."d" - Short date pattern.

2."D" - Long date pattern.

Related Questions