Convert a SQL Server datetime to a shorter date format

84
votes

I have a datetime column in SQL Server that gives me data like this 10/27/2010 12:57:49 pm and I want to query this column but just have SQL Server return the day month and year - eg. 2010 10 27 or something like that.

What are the functions I should be researching?

Should I be trying to convert to another date data type? Or simply convert it to a string?

10
sql-servertsqldatetime
Take a look at this article: msdn.microsoft.com/en-us/library/ms187928.aspx Specifically > Date and Time Styles - Brissles
Which version of SQL Server?? SQL Server 2008 introduced a load of new date and time related datatypes, e.g. DATE - so if you're on 2008, you can use SELECT CAST(YourDateTimeColumn AS DATE) and you get back just the date - no time portion. - marc_s
@marc_s meta.stackoverflow.com/questions/314561/… - apaul
Possible duplicate of How to remove the time portion of a datetime value (SQL Server)? - Michael Freidgeim

10 Answers

125
votes

Have a look at CONVERT. The 3rd parameter is the date time style you want to convert to.

e.g.

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) -- dd/MM/yyyy format
58
votes

Try this:

print cast(getdate() as date )

16
votes

If you need the result in a date format you can use:

Select Convert(DateTime, Convert(VarChar, GetDate(), 101))
11
votes

In addition to CAST and CONVERT, if you are using Sql Server 2008, you can convert to a date type (or use that type to start with), and then optionally convert again to a varchar:

declare @myDate date
set @myDate = getdate()
print cast(@myDate as varchar(10))

output:

2012-01-17
6
votes

With SQL Server 2005, I would use this:

select replace(convert(char(10),getdate(),102),'.',' ')

Results: 2015 03 05

6
votes

If you have a datetime field that gives the results like this 2018-03-30 08:43:28.177

Proposed: and you want to change the datetime to date to appear like 2018-03-30 

cast(YourDateField as Date)
5
votes

The shortest date format of mm/dd/yy can be obtained with:

Select Convert(varchar(8),getdate(),1)
0
votes

Just add date keyword. E.g. select date(orderdate),count(1) from orders where orderdate > '2014-10-01' group by date(orderdate);

orderdate is in date time. This query will show the orders for that date rather than datetime.

Date keyword applied on a datetime column will change it to short date.

0
votes

For any versions of SQL Server: dateadd(dd, datediff(dd, 0, getdate()), 0)

0
votes

The original DateTime field : [_Date_Time]

The converted to Shortdate : 'Short_Date'

CONVERT(date, [_Date_Time]) AS 'Short_Date'