To get records from a few days ago...
-
I have a very simple query that supposes to return a number
of records within the last 5 days, and for some strange reason,
I could not get it. It keeps giving me the error below.
Msg 4145, Level 15, State 1, Line 9
An expression of non-boolean type specified in a context where a condition is expected, near 5.
select id, name, changedate
from table1
where changedate - 5
-- changedate is datetime data type
I also tried to convert the date to mm/dd/yyyy format before
subtract the days, and it still give the same error.
What is wrong with my query? would appreciate any input.
-
Syntax issue.
Use this instead:
select id, name, changedatefrom table1
where changedate > GETDATE()-5
-
The where clause must be a condition that returns true or false. Your is not. changedate-5 is simply a value, it's not a condition. It will return a date 5 days less than changedate
Base on what you said, I'm guessing you want is something a bit more like this.
DECLARE @Today DATETIME, @FiveDaysAgo DATETIMESET @Today = (dd, datediff(dd,0, getdate()),0) -- today at midnight
SET @FiveDaysAgo = DATEADD(dd,-5,@Today) -- 5 days ago at midnight
select id, name, changedate
from table1
where changedate >= @FiveDaysAgo
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The problem is the '-' your where clause
where changedate - 5
this does not make sense, it should have a '=' , ' '
eg
where changedate = 5
-
you need to specify what you are comparing changedate - 5 with. at the moment, the query is saying that changedate - 5 should give either a true or false answer.
you would need something like:
select id, name, changedatefrom table1
where changedate >= dateadd(day, -5, getdate())
Life: it twists and turns like a twisty turny thing
-
I see, thanks so much Seth and Gail for your quick responses.
really appreciated.
-
Garadin (10/31/2008)
Syntax issue.Use this instead:
select id, name, changedatefrom table1
where changedate > GETDATE()-5
Beware of the time portion of dates. That, if run now, would miss any entries with a time before 4pm (local time for me). That may be what's wanted, it probably isn't though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Also see if you want this
select id, name, changedate
from table1
where changedate > DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),-5)
MadhivananFailing to plan is Planning to fail
-
GilaMonster (10/31/2008)
Garadin (10/31/2008)
Syntax issue.Use this instead:
select id, name, changedatefrom table1
where changedate > GETDATE()-5
Beware of the time portion of dates. That, if run now, would miss any entries with a time before 4pm (local time for me). That may be what's wanted, it probably isn't though.
Indeed. But I wouldn't have beaten you if I had typed all that! :hehe: (Kidding). It's a good reminder, I'm used to using it for larger, more general purposes such as GETDATE()-180, where the time isn't an important factor.
-
thank you all for your inputs, really helpful and appreciated a lot. i got it to work now by your suggestions, so you made my day...have a happy halloween.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply