2

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

please Help?

Improve this question
1
  • In future please state the database engine and version you're using. Commented Jul 14, 2009 at 21:48

10 Answers 10

Reset to default
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).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

Comments

3

Found this using Google

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

Improve this answer

4 Comments

I do find this amusing, but does anyone really need a link to Google these days? :-D
Some people apparently do :-D.
@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.
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.
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)
Improve this answer

2 Comments

COnverting to varchar and back is very slow.
@Alex that is true. Please see this post with performance testing proving this.
2 
-- Sneaky CAST/DATEDIFF trick strips off the time to get just the day (midnight)!
CAST(DATEDIFF(d,0,DateField) AS DATETIME) AS DayField
Improve this answer

Comments

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.

Improve this answer

Comments

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.

Improve this answer

Comments

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

Improve this answer

1 Comment

Please note that converting dates to varchar is slower than using DateDiff and DateAdd. See this post for more detail.
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.

Improve this answer

4 Comments

Why scalar? Inline ones perform much better.
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.
Using UDFs is convenient but poor practice because of it will kill performance compared to proper queries.
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.
0

datepart(day, datetimevalue)

Improve this answer

2 Comments

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.
full syntax is datepart(Year|Day|Month|Hour|...., datetimevalue). he can get the part and concart.....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.