Convert a SQL Server datetime to a shorter date format

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 Answers

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 

Try this:

print cast(getdate() as date )

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

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

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 

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) 

With SQL Server 2005, I would use this:

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

Results: 2015 03 05

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

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

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.

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

The original DateTime field : [_Date_Time]

The converted to Shortdate : 'Short_Date'

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