2

I have a variable of DateTime type in SQL. Just need to have Date part of it.

please Help?

AakashM
  • 62,551
  • 17
  • 151
  • 186
odiseh
  • 25,407
  • 33
  • 108
  • 151

10 Answers10

6
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

The result is: “2009-07-14 00:00:00.000”

Edit: guess the next variant is more common:

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

because of the day pattern can be easily changed to week or month pattern. It is very useful when the GROUP BY clause should group by week of month (reports).

Oleks
  • 31,955
  • 11
  • 77
  • 132
5

This has been asked and answered before on Stack Overflow. In fact, it's been asked over and over:

  1. Most efficient way in MS SQL to get date from date+time?
  2. Best way to check for current date in where clause of sql query.
  3. SQL Drop Time in DateTime
  4. MS SQL Date Only Without Time
  5. How to return the date part only from a SQL Server datetime datatype
Community
  • 1
  • 1
Rick
  • 4,575
  • 1
  • 26
  • 20
3

Found this using Google

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))

Colin
  • 10,630
  • 28
  • 36
  • 2
    I do find this amusing, but does anyone really need a link to Google these days? :-D – tags2k Jul 14 '09 at 11:57
  • 1
    Some people apparently do :-D. – Colin Jul 14 '09 at 12:05
  • @tags2k & Colin: I should say that I googled before putting my question here in stackoverflow but I didn't find any solution for my problem. If you replace a datetime value with GETDATE() in your above query (and time part uses ب.ظ or ق.ظ at the end ) your query won't execute properly. this is because I put the question here. Any way Thank you. – odiseh Jul 15 '09 at 07:52
  • In my opinion converting to float is poor practice, because a round-trip conversion to datetime is not reliable. Please see [this post for more detail](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991). – ErikE Sep 13 '10 at 00:02
3

If you just need a varchar representation of the date, you can use the convert function, e.g.

select convert(varchar, getDate(), 102) /* 2009.07.14 */

If you need a datetime (midnight on the given date), you can just convert it back.

select convert(datetime, convert(varchar, getDate(), 102), 102)
Patrick McElhaney
  • 57,901
  • 40
  • 134
  • 167
  • 1
    COnverting to varchar and back is very slow. – A-K Jul 14 '09 at 13:55
  • 2
    @Alex that is true. Please see [this post with performance testing proving this](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991). – ErikE Sep 13 '10 at 00:00
2
-- Sneaky CAST/DATEDIFF trick strips off the time to get just the day (midnight)!
CAST(DATEDIFF(d,0,DateField) AS DATETIME) AS DayField
n8wrl
  • 19,439
  • 4
  • 63
  • 103
2

SQL Server 2008 has a date datatype that stores just the date, if you are inthis version, perhaps this would be a better datat type for you to use. Be warned though, Date doesn't work exactly like datetime for data manipulation.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
1
SELECT DATEADD(day, DATEDIFF(day, '19900101', CURRENT_TIMESTAMP), '19900101')

A very useful article: "The purpose of this article is to explain how the datetime types work in SQL Server, including common pitfalls and general recommendations."The ultimate guide to the datetime datatypes

Note that converting to varchar and back (convert(datetime, convert(varchar, getDate(), 102), 102)) is much slower.

A-K
  • 16,804
  • 8
  • 54
  • 74
1

If you want the format 'MM/DD/YY', use "CONVERT(varchar, @datetimevalue, 1) to display just the date. If you need it in datetime format, use "CONVERT(datetime, CONVERT(varchar, @datetimevalue, 1))".

I created an entry in my SQL blog about how to retrieve and display all possible formats of the CONVERT(varchar, ..) function:

http://jessesql.blogspot.com/2009/04/converting-datetime-values-to-varchar.html

Jesse
  • 449
  • 4
  • 3
  • Please note that converting dates to varchar is slower than using DateDiff and DateAdd. See [this post for more detail](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991). – ErikE Sep 13 '10 at 00:03
1

A tip: If you find yourself doing this often, you can create a scalar User Defined Function containing the time-stripping logic of your choice.

Be warned: SQL Server 2000 has some painful bugs involving UDF's in ON clauses.

Isabelle Wedin
  • 1,345
  • 10
  • 26
  • Why scalar? Inline ones perform much better. – A-K Jul 14 '09 at 13:55
  • Very true. A deterministic CLR UDF may be even more efficient, but I've never tested this myself, and couldn't find anything through Google. – Isabelle Wedin Jul 14 '09 at 14:58
  • Using UDFs is convenient but poor practice because of it will kill performance compared to proper queries. – ErikE Sep 13 '10 at 00:04
  • I think it's a bit of an exaggeration to say it will "kill performance." There is a penalty, to be sure, but it is a small one. In this case, making a mistake in a delicate idiom is probably more expensive than conducting performance tests. In other words, proper profiling is a thousand times more important that worrying about hypothetical performance problems. I would say that eschewing tools that provide useful abstractions and help ensure correctness is "poor practice." SQL speed junkies may disagree. – Isabelle Wedin Sep 13 '10 at 13:18
0

datepart(day, datetimevalue)

  • That only gives the day of the month, for example today is the 16th of July, so datepart(day, getdate()) would return 16. I think he wants '2009-07-16' instead. – Rick Jul 15 '09 at 23:32
  • full syntax is datepart(Year|Day|Month|Hour|...., datetimevalue). he can get the part and concart..... –  Jul 21 '09 at 17:54