CODEWITHSUNDEEP
sqldatetimesql-server-2012
sql2015
sql2015

Reputation: 611

SQL data not returning based on date

I have a column in my database which stores a date as date/time. I have a SQL query to select all records where the date matches the one in the query, but it is not returning any data.

select Name,Dateadded from table

this returns results like

Bob Smith  2009-12-11 09:35:53.000

I changed my query to be the below and no results are returned.

select Name,Dateadded from table where dateadded = '2009-12-11'

I tried the converting to date and still no luck, I have to enter in a date between query to get it working

SELECT
Name,  convert(varchar(10), Dateadded , 103)
FROM
table

can anyone tell me where I'm going wrong? I have tried using 'like' and still does not work if I do 'between' two date ranges it works

Upvotes: 0

Views: 1532

Answers (6)

Wolkowsky
Wolkowsky

Reputation: 68

It may be like this:

WHERE Dateadded >= '2014-07-24' AND Dateadded < '2014-07-25'
WHERE Dateadded >= '2014-07-24' AND Dateadded < DATEADD(dd, 1, '2014-07-24')

or with convert:

WHERE convert(date,Dateadded) = '2014-07-24'

and also you can do something like that

WHERE DAY(Dateadded) = 24 AND MONTH(Dateadded) = 07 AND YEAR(Dateadded) = 2014

Upvotes: 3

HoneyBadger
HoneyBadger

Reputation: 15150

Casting to date as in other answers will work. For performance, it may be better (depending on indexes) to use:

select Name,Dateadded 
from   [table] 
where  dateadded >= '2009-12-11'
   and dateadded < '2009-12-12'

Upvotes: 1

Vamshi Maroju
Vamshi Maroju

Reputation: 1

The following codes will work:

SELECT Name,Dateadded FROM table WHERE Dateadded LIKE '2009-12-11%'

or

SELECT Name,Dateadded FROM table WHERE Dateadded >= '2009-12-11 00:00:00' AND Dateadded <= '2009-12-11 23:59:59'

or

SELECT Name,Dateadded FROM table WHERE Dateadded BETWEEN '2009-12-11 00:00:00' AND '2009-12-11 23:59:59'

Upvotes: 0

freakyhat
freakyhat

Reputation: 471

Convert datetime to date in your where clause:

select Name,Dateadded
from table 
where Convert(date, dateadded) = '2009-12-11'

Upvotes: 2

Gaur93
Gaur93

Reputation: 695

You can try 

select Name,Dateadded from table where date(dateadded) = '2009-12-11'

as you are trying to match only date.

Note : after seeing comments , it seems you are using sql server 2012, above works in mysql not sure about sql server 2012

Upvotes: 0

I.Manev
I.Manev

Reputation: 727

Use = only when you expect exact match. In your case you can use :

select Name,Dateadded from table where dateadded like '2009-12-11%'

Upvotes: 0

Related Questions