0

When I select two rows with a DATETEIME stamp, I only want the m/d/y data and nothing after that.

It has to changed during the select (not afterwards).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tdjfdjdj
  • 2,391
  • 13
  • 44
  • 71
  • TSQL is used by both SQL Server and Sybase -- which is it? It can drastically impact the answer. – OMG Ponies Jul 05 '11 at 19:47
  • Do you only want the date component of the date, or are you trying to format a string right in the query? – Kieren Johnstone Jul 05 '11 at 19:47
  • Assuming SQL Server, majority of the native TSQL options are done via [CAST/CONVERT](http://msdn.microsoft.com/en-us/library/ms187928.aspx) if not crafting by hand. – OMG Ponies Jul 05 '11 at 19:48

2 Answers2

3

To remove the time you just need to do the following Assuming SQL Server

Pre SQL 2008

select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) yourdate
FROM yourtable

SQL 2008

select CAST(getdate() as date) yourdate
FROM yourtable

See Most efficient way in SQL Server to get date from date+time? or

Best approach to remove time part of datetime in SQL Server

Community
  • 1
  • 1
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • conrad...any trick as to how you get the Q so quickly? 3 mins and with the answer as well...nice! – Ram Jul 05 '11 at 19:50
  • @Ram. This just happens to be one of the problems that I face regularly so I have the syntax handy. The links took a little longer but I added them within the first five minutes so you don't get to see the edits. – Conrad Frix Jul 05 '11 at 20:01
1

Is this for export? If you only want the text you can use a variety of coversion formats available on MSDN.

select convert(varchar, getdate(), 101)
-- output: 07/05/2011

Otherwise, if you're using sql 2008, you can just cast the datetime to date:

select cast(getdate() as date)
canon
  • 40,609
  • 10
  • 73
  • 97
  • I have to compare the times in my WHERE clause so I think I have to keep them as timestamps. Is there another way to do this in 2005? – tdjfdjdj Jul 05 '11 at 19:54
  • You could cast back to datetime via `select cast(convert(varchar, getdate(), 101) as datetime)` but I'm not sure how that would perform versus a datediff solution. What are you really getting at, though? Do you really only need this to query against a particular day? If that's the case, let me know because there's a better way. – canon Jul 05 '11 at 20:00
  • I have two dates I am selecting, and I use a WHERE to determine which row has Date greater than another Date. Both dates have to be converted as m/d/y. – tdjfdjdj Jul 05 '11 at 20:17