Hi I have a variable of DateTime type in SQL. Just need to have Date part of it.
please Help?
+1
A:
-- Sneaky CAST/DATEDIFF trick strips off the time to get just the day (midnight)!
CAST(DATEDIFF(d,0,DateField) AS DATETIME) AS DayField
n8wrl
2009-07-14 11:31:16
+4
A:
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).
Alex
2009-07-14 11:32:04
+2
A:
Found this using Google
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
Colin
2009-07-14 11:33:13
I do find this amusing, but does anyone really need a link to Google these days? :-D
tags2k
2009-07-14 11:57:33
Some people apparently do :-D.
Colin
2009-07-14 12:05:02
odiseh
2009-07-15 07:52:57
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).
Emtucifor
2010-09-13 00:02:18
+3
A:
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
2009-07-14 11:53:48
@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).
Emtucifor
2010-09-13 00:00:26
A:
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.
AlexKuznetsov
2009-07-14 13:16:41
A:
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
2009-07-14 13:48:43
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).
Emtucifor
2010-09-13 00:03:53
A:
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.
WCWedin
2009-07-14 13:52:57
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.
WCWedin
2009-07-14 14:58:02
Using UDFs is convenient but poor practice because of it will kill performance compared to proper queries.
Emtucifor
2010-09-13 00:04:41
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.
WCWedin
2010-09-13 13:18:03
+1
A:
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
2009-07-14 14:44:55
+3
A:
This has been asked and answered before on Stack Overflow. In fact, it's been asked over and over:
Rick
2009-07-15 04:52:51
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
2009-07-15 23:32:36