hidrau
asked on
FILTER BY DATE FIELD sql 2005
Hello Guys,
I am having problem to filter through datetime field
I was doing this:
Select * from Table
where convert(char(10),datefield ,112)='200 90403'
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
I am having problem to filter through datetime field
I was doing this:
Select * from Table
where convert(char(10),datefield
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
use
select * from table
where datediff(dd,datefield,'200 8-04-01') = 0
select * from table
where datediff(dd,datefield,'200
udayakumarlm,
FYI, Even your query wont use the Index on the datefield column.
FYI, Even your query wont use the Index on the datefield column.
small correction
select datefield from table
where datediff(dd,datefield,'200 8-04-01') = 0 will use index
not
select * from table
where datediff(dd,datefield,'200 8-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.
select datefield from table
where datediff(dd,datefield,'200
not
select * from table
where datediff(dd,datefield,'200
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.
ASKER
thanks
Open in new window