?
Solved

FILTER BY DATE FIELD sql 2005

Posted on 2009-04-06
6
Medium Priority
?
497 Views
Last Modified: 2012-05-06
Hello Guys,

I am having problem to filter through datetime field

I was doing this:

Select * from Table
where convert(char(10),datefield,112)='20090403'

But doing this I will have my index off

The dates that I have recorded is in this format:

2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00

I have a time together

How can I filter the rows ?
I tried this but it didn't work

select * from table
where datefield='2009-04-21'

None rows

Thanks
0
Comment
Question by:hidrau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24076103
this should do:
Select * from Table
where datefield >= convert(datetime,'20090403', 112)
  and datefield < dateadd(day,1,convert(datetime,'20090403', 112))

Open in new window

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24076113
This wont work

select * from table
where datefield='2009-04-21'

as the value '2009-04-21' will be converted into datetime value of '2009-04-21 00:00:00.000' which is not equal to your date value.

Using a scalar function convert will not use the Index on that column.
In order to use your index effectively your query should be something like this:

select * from table
where datefield between cast('2009-04-21' as datetime) and DATEADD (DD, 1, DATEADD(MS, -2, CAST('2009-04-21' as datetime)))
0
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24076128
use
select * from table
where datediff(dd,datefield,'2008-04-01') = 0
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24076149
udayakumarlm,
   FYI, Even your query wont use the Index on the datefield column.
0
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24076254
small correction
select datefield from table
where datediff(dd,datefield,'2008-04-01') = 0 will use index
not
select * from table
where datediff(dd,datefield,'2008-04-01') = 0
hidrau, if it is performace that you are looking at then don't use *, if the fields you specify instead of * are all indexed then SQL will use index scan else it will use clustered index scan.
0
 
LVL 1

Author Closing Comment

by:hidrau
ID: 31566965
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question