CODEWITHSUNDEEP
sqlsql-serverdatetime
user3770612
user3770612

Reputation: 1769

How to Search datetime in Sql server 2008R2

i am having problem with a sql search . i am trying to search with the help of datetime because in my project time is very important. when i use query like this

where registration_date = '2014-06-19 00:12:08.940'

it does works but as soon as i try to search using

where registration_date like '2014-06-19%'

i get no results. because i want to search for all the registration taken place on a specific date including time, i am assuming that i dont know the specific time...

Upvotes: 3

Views: 195

Answers (5)

g2server
g2server

Reputation: 5367

You will have to cast to date to compare two dates on equality. eg

CAST(registration_date AS DATE) = CAST('2014-06-19' AS DATE)

An alternative:

DECLARE @DateToFilter DATE = (CAST('2014-06-19' AS DATE))    
[..]
WHERE registration_date >= @DateToFilter
AND registration_date < DATEADD(d, 1, @DateToFilter)

EDIT: Regarding performance, and assuming an index exists on the date column, the index can be used in both cases (yes, CAST(x AS DATE) is SARGable). There is a very interesting analysis on the performance differences here and here. As always, test.

Upvotes: 1

Ajay2707
Ajay2707

Reputation: 5798

Eigher you cast both part, so all data which have same date find like

CAST(registration_date AS DATE) = CAST('2014-06-19' AS DATE)
or 
Convert(varchar(10), registration_date,103) = Convert(varchar(10),'2014-06-19' ,103)

or you can search as above date range 

 registration_date 
 between cast( getdate() as date ) and
 dateadd(dd, 1, cast( getdate() as date ))

Upvotes: 0

Azar
Azar

Reputation: 1867

Try this

where convert(date,registration_date) = '2014-06-19'

It works for between also

Upvotes: 0

ta.speot.is
ta.speot.is

Reputation: 27214

Simply search for everything that's at least on or after that date, and earlier than the next day.

    registration_date >= '2014-06-19'
AND registration_date < '2014-06-20'

Upvotes: 3

Pred
Pred

Reputation: 9042

Search with intervals:

WHERE registration_date >= '2014-06-19 00:00:00' AND registration_date < '2014-06-20 00:00:00'

When you search for a specific time, use the same value in both conditions, when you search for a day, use the above format.

Upvotes: 1

Related Questions