NOTE: This answer returns the original DATETIME or DATETIME2 type. For an expression that returns a true DATE type (SQL Server 2008 and later), see BenR's answer below.
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
for example
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
gives me
2008-09-22 00:00:00.000
Pros:
- No varchar<->datetime conversions required
- No need to think about locale
NOTE: This answer returns the original DATETIME or DATETIME2 type. For an expression that returns a true DATE type (SQL Server 2008 and later), see BenR's answer below.
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
for example
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
gives me
2008-09-22 00:00:00.000
Pros:
- No varchar<->datetime conversions required
- No need to think about locale
SQLServer 2008 now has a 'date' data type which contains only a date with no time component. Anyone using SQLServer 2008 and beyond can do the following:
SELECT CONVERT(date, GETDATE())
SELECT Datetime values using where clause
How to query DATETIME field using only date in Microsoft SQL Server? - Stack Overflow
sql server - Extract Date Portion Only From DateTime Field - Database Administrators Stack Exchange
sql - Using a date value in where clause on a datetime data type - Stack Overflow
WHERE datetime_column >= '20081220 00:00:00.000'
AND datetime_column < '20081221 00:00:00.000'
First of all, I'd recommend using the ISO-8601 standard format for date/time - it works regardless of the language and regional settings on your SQL Server. ISO-8601 is the YYYYMMDD format - no spaces, no dashes - just the data:
select * from tblErrorLog
where errorDate = '20081220'
Second of all, you need to be aware that SQL Server 2005 DATETIME always includes a time. If you check for exact match with just the date part, you'll get only rows that match with a time of 0:00:00 - nothing else.
You can either use any of the recommend range queries mentioned, or in SQL Server 2008, you could use the DATE only date time - or you could do a check something like:
select * from tblErrorLog
where DAY(errorDate) = 20 AND MONTH(errorDate) = 12 AND YEAR(errorDate) = 2008
Whichever works best for you.
If you need to do this query often, you could either try to normalize the DATETIME to include only the date, or you could add computed columns for DAY, MONTH and YEAR:
ALTER TABLE tblErrorLog
ADD ErrorDay AS DAY(ErrorDate) PERSISTED
ALTER TABLE tblErrorLog
ADD ErrorMonth AS MONTH(ErrorDate) PERSISTED
ALTER TABLE tblErrorLog
ADD ErrorYear AS YEAR(ErrorDate) PERSISTED
and then you could query more easily:
select * from tblErrorLog
where ErrorMonth = 5 AND ErrorYear = 2009
and so forth. Since those fields are computed and PERSISTED, they're always up to date and always current, and since they're peristed, you can even index them if needed.
use range, or DateDiff function
select * from test
where date >= '20140319' and date < '20140320'
or
select * from test
where datediff(day, date, '03/19/2014') = 0
Other options are:
If you have control over the database schema, and you don't need the time data, take it out.
or, if you must keep it, add a computed column attribute that has the time portion of the date value stripped off...
Alter table Test Add DateOnly As DateAdd(day, datediff(day, 0, date), 0)
or, in more recent versions of SQL Server...
Alter table Test Add DateOnly As Cast(DateAdd(day, datediff(day, 0, date), 0) as Date)
then, you can write your query as simply:
select * from test
where DateOnly = '03/19/2014'
Simple answer;
select * from test where cast ([date] as date) = '03/19/2014';
If you pass a string like '20190604' to a datetime then yes, it'll be interpreted as the date and time 2019-06-04T00:00:00.000. This means that if you have any values that aren't on the stroke of midnight then the row won't be returned (even if that value is 2019-06-04T00:00:00.003).
If you do have values other than at midnight and you want to return all rows for a specific date you can use CONVERT/CAST to change the data type of the column to date; which is still SARGable:
SELECT DateField
FROM CalendarTbl
WHERE CONVERT(date,DateField) = '20190604';
Yes it's fine. Your string gets implicitly cast to the needed datetime. I'd wager it's the fastest way for human-inputting dates.
Check the colored table in the the official documentation for a list of implicit and explicit casts.
The fastest if you have to iterate over a recordset and don't have date in SQL Server 2008
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
Two different and excellent answers on StackOverflow bear this out: One, Two
Varchar conversions are one of the worst ways to do it. Of course, for one value it may not matter but it's a good habit to get into.
This way is also deterministic, say if you want to index a computed column. Even folk who write books about SQL Server get caught out with datetime conversions
This technique is also extendable.
- yesterday:
DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1) - start of month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) - end of last month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1) - start of next month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)
Edit:
As I mentioned about determinism, varchar methods are not safe unless you use style 112.
Other answers here point out that you'd never apply this to a column. This is correct, but you may want to select a 100k rows or add a computed column or GROUP BY dateonly. Then you have to use this method.
The other answer also mentions style 110. This is not language or SET DATEFORMAT safe and fails with "british" language setting. See the ultimate guide to the datetime datatypes by Tibor Karaszi.
You have to convert it to varchar specifying a format pattern (110 in this case) then convert (or cast) back to datetime.
select getdate(), cast(convert(varchar(10), getdate(), 110) as datetime)