Need only Date from DateTime

问题:

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

please Help?

回答1:

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).

回答2:

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

回答3:

Found this using Google

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

回答4:

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)

回答5:

-- Sneaky CAST/DATEDIFF trick strips off the time to get just the day (midnight)!
CAST(DATEDIFF(d,0,DateField) AS DATETIME) AS DayField

回答6:

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.

回答7:

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.

回答8:

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

回答9:

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.

回答10:

datepart(day, datetimevalue)