87

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?

piet.t
  • 11,718
  • 21
  • 43
  • 52
kaes
  • 1,267
  • 2
  • 13
  • 17
  • 6
    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 Jan 17 '12 at 20:25
  • 2
    Take a look at this article: http://msdn.microsoft.com/en-us/library/ms187928.aspx Specifically > Date and Time Styles – Brissles Jan 17 '12 at 20:21
  • 1
    @marc_s http://meta.stackoverflow.com/questions/314561/are-votes-to-a-question-comment-more-valuable-than-votes-for-an-answer – apaul Jan 12 '16 at 21:33
  • 1
    Possible duplicate of [How to remove the time portion of a datetime value (SQL Server)?](http://stackoverflow.com/questions/2775/how-to-remove-the-time-portion-of-a-datetime-value-sql-server) – Michael Freidgeim Aug 31 '16 at 05:47

10 Answers10

136

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
NotMe
  • 87,343
  • 27
  • 171
  • 245
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
64

Try this:

print cast(getdate() as date )

snollygolly
  • 1,858
  • 2
  • 17
  • 31
jabu.hlong
  • 2,194
  • 20
  • 21
  • 5
    I like this option more than the accepted answer. It leaves the column in a Date format so that my 'order by' works correctly instead of just a varchar. – Milne Jun 21 '17 at 16:22
  • 2
    This does not work on SQL Server 2005 and earlier. The DATE data type was introduced with SQL 2008. – JerryOL Jan 24 '18 at 19:35
  • As stated above it is indeed nice to get a date data-type, but when a specific format is expected (like "dd MM yyyy") then more is needed. – trincot Dec 27 '18 at 18:49
15

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

Select Convert(DateTime, Convert(VarChar, GetDate(), 101))
Israel Margulies
  • 8,656
  • 2
  • 30
  • 26
11

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
CD Jorgensen
  • 1,361
  • 9
  • 8
7

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)
BIReportGuy
  • 799
  • 3
  • 13
  • 36
  • 1
    you haven't added anything new, jabu.hlong already gave this answer 4 years ago... – Pawel Czapski May 11 '18 at 14:32
  • It is not assured that the date will be rendered in the OP's format. It might be yyyy-MM-dd, dd/MM/yyyy, dd/MM/yyyy 00:00:00, or still something else. – trincot Dec 27 '18 at 18:46
6

With SQL Server 2005, I would use this:

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

Results: 2015 03 05

Konrad Krakowiak
  • 12,285
  • 11
  • 58
  • 45
AGuest
  • 61
  • 1
  • 1
5

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

Select Convert(varchar(8),getdate(),1)
bsivel
  • 2,821
  • 5
  • 25
  • 32
0

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.

Anuj Kaul
  • 21
  • 1
0

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

Andrey
  • 34
  • 1
  • It is not assured that the date will be rendered in the OP's format. It might be yyyy-MM-dd, dd/MM/yyyy, dd/MM/yyyy 00:00:00, or still something else. – trincot Dec 27 '18 at 18:47
0

The original DateTime field : [_Date_Time]

The converted to Shortdate : 'Short_Date'

CONVERT(date, [_Date_Time]) AS 'Short_Date'
  • This returns a date data type which will be left to the interface to render as a string, so it may well produce "27/12/2018 00:00:00". That's not what the OP asked for. – trincot Dec 27 '18 at 17:56
  • This what he aimed to get -- dd/MM/yyyy ; and this what my answer produces 2018-11-20 – Yousef Albakoush Dec 27 '18 at 18:22
  • Well first of all 2018-11-20 is *not* dd/MM/yyyy but yyyy-MM-dd. But the rendering is really system (and locale) dependent. On my system it produces also hours, minutes and seconds (all zero). This is because you did not tell SQL how the date should be represented as string, and the OP needs an exact string format. – trincot Dec 27 '18 at 18:25
  • Firstly, 2018-11-20 is because my PC is configured for Arabic language use ; Secondly, i will try it after changing the short date in my PC – Yousef Albakoush Dec 27 '18 at 18:42