September 10, 2012 at 7:52 am
Hello,
I'm trying to write a query that will give me info from just 3 days ago without having to keep changing the getdate.
September 10, 2012 at 7:55 am
WHERE datetoquery >= dateadd(d,-3,getdate())
If datetoquery is a dateTIME stamp you may need to also strip the time off the getdate()
September 10, 2012 at 7:58 am
This was removed by the editor as SPAM
September 10, 2012 at 8:02 am
aaron.reese (9/10/2012)
WHERE datetoquery >= dateadd(d,-3,getdate())If datetoquery is a dateTIME stamp you may need to also strip the time off the getdate()
Which can be done one of two ways in SQL Server 2008 and later.
WHERE datetoquety >= cast(dateadd(dd, -3, getdate()) as date) -- SQL Server 2008 and later only
WHERE datetoquety >= dateadd(dd, datediff(dd, 0, getdate()) - 3, 0) -- will work in SQL Server 2000/2005 and later
September 10, 2012 at 8:05 am
Here is my query:
SELECT dbo.RHeads.Branch, dbo.RLines.Part, dbo.Stock.Free AS [On Hand], dbo.RLines.CQty AS [Qty Last Received],
dbo.RHeads.DateTime AS Date,
dbo.RLines.Unit AS Cost, dbo.Stock.Avg AS [Norton Avg Cost], dbo.RHeads.Supp, dbo.RHeads.[Document],
dbo.Stock.Min, dbo.Stock.Max
FROM dbo.RHeads INNER JOIN
dbo.RLines ON dbo.RHeads.[Document] = dbo.RLines.[Document] INNER JOIN
dbo.Stock ON dbo.RHeads.Branch = dbo.Stock.Branch AND dbo.RLines.Part = dbo.Stock.Part AND dbo.RLines.Branch = dbo.Stock.Branch
WHERE (dbo.RHeads.Supp NOT IN ('14541', '3050', '3502', '36516', '58843', '77814', '84610', '101835', '56092', '77300'))
and dateadd(day, -30, getdate())
AND (dbo.RLines.Part NOT LIKE '*%') AND (NOT (dbo.RHeads.Corder LIKE 't%')) AND (dbo.RLines.Part NOT LIKE 'Freight')
ORDER BY dbo.RHeads.Branch, dbo.RLines.Part
and here is the error:
Msg 4145, Level 15, State 1, Line 13
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
September 10, 2012 at 8:07 am
sstteevvee22 (9/10/2012)
Here is my query:SELECT dbo.RHeads.Branch, dbo.RLines.Part, dbo.Stock.Free AS [On Hand], dbo.RLines.CQty AS [Qty Last Received],
dbo.RHeads.DateTime AS Date,
dbo.RLines.Unit AS Cost, dbo.Stock.Avg AS [Norton Avg Cost], dbo.RHeads.Supp, dbo.RHeads.[Document],
dbo.Stock.Min, dbo.Stock.Max
FROM dbo.RHeads INNER JOIN
dbo.RLines ON dbo.RHeads.[Document] = dbo.RLines.[Document] INNER JOIN
dbo.Stock ON dbo.RHeads.Branch = dbo.Stock.Branch AND dbo.RLines.Part = dbo.Stock.Part AND dbo.RLines.Branch = dbo.Stock.Branch
WHERE (dbo.RHeads.Supp NOT IN ('14541', '3050', '3502', '36516', '58843', '77814', '84610', '101835', '56092', '77300'))
and dateadd(day, -30, getdate())
AND (dbo.RLines.Part NOT LIKE '*%') AND (NOT (dbo.RHeads.Corder LIKE 't%')) AND (dbo.RLines.Part NOT LIKE 'Freight')
ORDER BY dbo.RHeads.Branch, dbo.RLines.Part
and here is the error:
Msg 4145, Level 15, State 1, Line 13
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
I am assuming your error is where I bolded. Look up NOT keyword in Books Online.
September 10, 2012 at 8:11 am
This was removed by the editor as SPAM
September 10, 2012 at 8:16 am
Stewart hit the error. I think you can see it better when you look at the code as I have it formatted:
SELECT
rh.Branch,
rl.Part,
s.Free AS [On Hand],
rl.CQty AS [Qty Last Received],
rh.DateTime AS Date,
rl.Unit AS Cost,
s.Avg AS [Norton Avg Cost],
rh.Supp,
rh.[Document],
s.Min,
s.Max
FROM
dbo.RHeads rh
INNER JOIN dbo.RLines rl
ON rh.[Document] = rl.[Document]
INNER JOIN dbo.Stock s
ON rh.Branch = s.Branch AND
rl.Part = s.Part AND
rl.Branch = s.Branch
WHERE
rh.Supp NOT IN ('14541', '3050', '3502', '36516', '58843', '77814', '84610', '101835', '56092', '77300')
and dateadd(day, -30, getdate())
AND (rl.Part NOT LIKE '*%')
AND (NOT (rh.Corder LIKE 't%'))
AND (rl.Part NOT LIKE 'Freight')
ORDER BY
rh.Branch,
rl.Part;
You will also note I added table aliases to the tables in the FROM clause and used those in your select list. For your information, the use of three part names in a SELECt list has been depreciated by Microsoft and using them (dbo.tablename.column) may be removed from a future version of SQL Server. I recommend changing your coding to style to something like I have done above.
September 10, 2012 at 8:26 am
Thanks Lynn. Using your code, now I get this:
Msg 4145, Level 15, State 1, Line 24
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
September 10, 2012 at 8:28 am
sstteevvee22 (9/10/2012)
Thanks Lynn. Using your code, now I get this:Msg 4145, Level 15, State 1, Line 24
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
Have you fixed this:
and dateadd(day, -30, getdate())
You need to compare this date calculation to something. That is what Stewart also said in his post.
September 10, 2012 at 8:31 am
That is because you still havn't fixed the error 😀
In the second line of the WHERE clause you have said to calculate getdate() less 30 days, but you have not said which field should be compared.
September 10, 2012 at 8:31 am
Stewart "Arturius" Campbell (9/10/2012)
The issue relates to the
and dateadd(day, -30, getdate())section of the where clause.
And apparently I don't remember how to read this morning. DUH.
Good catch.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply