96
Select * from [User] U
where  U.DateCreated = '2014-02-07'

but in the database the user was created on 2014-02-07 12:30:47.220 and when I only put '2014-02-07'

It does not show any data

Dyrandz Famador
  • 4,499
  • 5
  • 25
  • 40
Muhabutti
  • 1,256
  • 2
  • 15
  • 20

6 Answers6

99

DON'T be tempted to do things like this:

Select * from [User] U where convert(varchar(10),U.DateCreated, 120) = '2014-02-07'

This is a better way:

Select * from [User] U 
where U.DateCreated >= '2014-02-07' and U.DateCreated < dateadd(day,1,'2014-02-07')

see: What does the word “SARGable” really mean?

EDIT + There are 2 fundamental reasons for avoiding use of functions on data in the where clause (or in join conditions).

  1. In most cases using a function on data to filter or join removes the ability of the optimizer to access an index on that field, hence making the query slower (or more "costly")
  2. The other is, for every row of data involved there is at least one calculation being performed. That could be adding hundreds, thousands or many millions of calculations to the query so that we can compare to a single criteria like 2014-02-07. It is far more efficient to alter the criteria to suit the data instead.

"Amending the criteria to suit the data" is my way of describing "use SARGABLE predicates"

And do not use between either.

the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201' This form works with all types and all precisions, regardless of whether the time part is applicable.

http://sqlmag.com/t-sql/t-sql-best-practices-part-2 (Itzik Ben-Gan)

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • 2
    If you have some time you should explain why one is better than the other (index usage), that will make this a better answer – Serpiton Aug 29 '14 at 09:14
  • easier said than done, there are several data types (date, datetime, datetime2, smalldatetime) plus different mssql versions to consider. One answer does not fit all I'm afraid. But I shall add a note. – Paul Maxwell Aug 29 '14 at 09:19
  • 1
    If you utilise cast(datetime as date) it will still use an index on the datetime field. try it. – Steve Ford Aug 29 '14 at 09:27
  • @Steve Ford, I agree - in latter versions of mssql - that it why I said "it's easier said than done"; it's v.hard to be right for all conditions. It is however a good practice to use sargable predicates even if optimizers `can` sometimes compensate. – Paul Maxwell Aug 29 '14 at 09:34
  • @SteveFord you're right, but IMO before learning the special case one should learn the general one. The general case is that using a `CAST` on a indexed column remove the index from the optimizer, also the second reason pointed out in the answer can be quite a bother with large dataset. – Serpiton Aug 29 '14 at 12:09
  • @Serpiton thanks, I understand and appreciate your comments and changes to your post. The second reason doesn't apply in this case as there are only 2 additional calculations, when the where clause is expanded to col > startdate and col < enddate. with startdate and enddate being calculated once each. – Steve Ford Aug 29 '14 at 13:03
  • Only thing to remember is date format in sql server is 'YYYYMMDD' OR 'YYYY-MM-DD'. – V.J. Sep 23 '15 at 13:04
  • 2
    the safest `date literal` format is 'YYYYMMDD'. Data types: date or datetime or time or datetime2 or smalldatetime do NOT have a format all as they are stored as numbers. For date literals 'YYYY-MM-DD' is NOT entirely safe there is one locale setting that can misinterpret that sequence to be YYYY-DD-MM – Paul Maxwell Sep 24 '15 at 23:39
88

If you are on SQL Server 2008 or later you can use the date datatype:

SELECT *
FROM [User] U
WHERE CAST(U.DateCreated as DATE) = '2014-02-07'

It should be noted that if date column is indexed then this will still utilise the index and is SARGable. This is a special case for dates and datetimes.

enter image description here

You can see that SQL Server actually turns this into a > and < clause:

enter image description here

I've just tried this on a large table, with a secondary index on the date column as per @kobik's comments and the index is still used, this is not the case for the examples that use BETWEEN or >= and <:

SELECT *
FROM [User] U
WHERE CAST(U.DateCreated as DATE) = '2016-07-05'

showing index usage with secondary index

Steve Ford
  • 7,433
  • 19
  • 40
  • it may have seemed I was targeting this answer, that was not the case as we posted at almost the same time and I was unaware of your answer. But to reduce the need for you increase the size of your image again I have change my "do not do" example, perhaps this will help? – Paul Maxwell Aug 29 '14 at 12:53
  • How is `AaronsBad` database going, these days? – Pure.Krome Jun 07 '16 at 03:18
  • @Pure.Krome as you can tell that was a temporary db which no longer exists. – Steve Ford Jun 07 '16 at 11:59
  • 1
    *"It should be noted that if date column is indexed then this will still utilise the index and is SARGable"* - This is true when DateCreated is PK or clustered Index. for non-clustered index there is a full table scan. – kobik Jul 05 '16 at 12:22
  • @kobik in fact I've just done a test and this solution uses an index for a large table. The other solutions don't. – Steve Ford Jul 05 '16 at 16:21
3

According to your query Select * from [User] U where U.DateCreated = '2014-02-07'

SQL Server is comparing exact date and time i.e (comparing 2014-02-07 12:30:47.220 with 2014-02-07 00:00:00.000 for equality). that's why result of comparison is false

Therefore, While comparing dates you need to consider time also. You can use
Select * from [User] U where U.DateCreated BETWEEN '2014-02-07' AND '2014-02-08'.

imdzeeshan
  • 1,098
  • 20
  • 26
  • 2
    Unfortunately SQL defines `between` with equals at both ends (>= and <=) e.g. `select * from x where col_1 between 'A' and 'M'` includes A and M (plus B to L of course). That inclusive nature of `between` is terrific for something like a document index; but for date/time ranges it is a disaster because it creates overlaps. I know using between looks great, but in truth an `SQL between` just does not work properly for date ranges. Always use a combination of >= with < as in `U.DateCreated >='2014-02-07' and U.DateCreated < '2014-02-08'` – Paul Maxwell Sep 15 '15 at 08:16
  • @Used_By_Already Agree with you, but for this particular case (in question), `between` is OK. your suggestion to use `>=` and `<` is no doubt preferable over `between` for date ranges. I just want to say that we should consider time also when we compare dates. – imdzeeshan Sep 16 '15 at 12:28
1

Of-course this is an old thread but to make it complete.

From SQL 2008 you can use DATE datatype so you can simply do:

SELECT CONVERT(DATE,GETDATE())

OR

Select * from [User] U
where  CONVERT(DATE,U.DateCreated) = '2014-02-07'
Bikram
  • 828
  • 8
  • 21
-2

Please try this. This query can be used for date comparison

select * from [User] U where convert(varchar(10),U.DateCreated, 120) = '2014-02-07'
SnowWhite
  • 190
  • 1
  • 3
  • 9
-3

You can use LIKE statement instead of =. But to do this with DateStamp you need to CONVERT it first to VARCHAR:

SELECT * 
FROM [User] U
WHERE CONVERT(VARCHAR, U.DateCreated, 120) LIKE '2014-02-07%'
WhiteAngel
  • 2,594
  • 2
  • 21
  • 35
  • 1
    +1 since this is a working solutions, and can be used in many scenarios, please don't down vote working answers just because you feel it's not a good practice, provide us with the a reliable article or doc, then you convince us more – amd Nov 26 '15 at 13:23
  • 4
    @Ahmad String conversion followed by comparison is more expensive than date comparison. So the downvotes are warranted, as this is not a good way of doing what the OP wants to do. – mbomb007 Nov 21 '16 at 19:31