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!

Just do:

SELECT CAST(date_variable AS date)

or with with PostgreSQL:

SELECT date_variable::date

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

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

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

On SQL Server 2008 and higher, you should CONVERT to date:

SELECT CONVERT(date, getdate())

On older versions, you can do the following:

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

As suggested by Michael

Use this variant: SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

select getdate()

SELECT DATEADD(hh, DATEDIFF(hh, 0, getdate()), 0)
SELECT DATEADD(hh, 0, DATEDIFF(hh, 0, getdate()))

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

SELECT DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)
SELECT DATEADD(mm, 0, DATEDIFF(mm, 0, getdate()))

SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)
SELECT DATEADD(yy, 0, DATEDIFF(yy, 0, getdate()))

Output:

2019-04-19 08:09:35.557

2019-04-19 08:00:00.000
4763-02-17 00:00:00.000

2019-04-19 00:00:00.000
2019-04-19 00:00:00.000

2019-04-01 00:00:00.000
1903-12-03 00:00:00.000

2019-01-01 00:00:00.000
1900-04-30 00:00:00.000

select convert(getdate() as date)

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

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

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

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.

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

1. SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 103)) -----dd/mm/yyyy

2. SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))------mm/dd/yyyy

3. SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 102))

select cast(createddate as date) as derivedate from table 

createdate is your datetime column , this works for sqlserver

My Style

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

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.

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

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

 SELECT CAST(GETDATE() AS DATE)

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

On SQL Server 2000

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

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

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

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:

1. Standard Date and Time Format Strings
2. Custom Date and Time Format Strings

Starting from SQL SERVER 2012, you can do this:

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

Date:

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

Time:

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

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

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

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; 

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

If using SQL 2008 and above:

select cast(getdate() as date)

To obtain the result indicated, I use the following command.

SELECT CONVERT(DATETIME,CONVERT(DATE,GETDATE()))

I holpe it is useful.

SELECT * FROM tablename WHERE CAST ([my_date_time_var] AS DATE)= '8/5/2015'

I favor the following which wasn't mentioned:

DATEFROMPARTS(DATEPART(yyyy, @mydatetime), DATEPART(mm, @mydatetime), DATEPART(dd, @mydatetime))

It also doesn't care about local or do a double convert -- although each 'datepart' probably does math. So it may be a little slower than the datediff method, but to me it is much more clear. Especially when I want to group by just the year and month (set the day to 1).

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

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

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101))

Edit: The first two methods are essentially the same, and out perform the convert to varchar method.