Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Syntax error related to "WHERE" Expand / Collapse
Author
Message
Posted Saturday, October 26, 2013 2:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, December 28, 2013 6:04 AM
Points: 107, Visits: 940
Hi
I have a store procedure, it works fine, but I want to make a change in it, I want to have only the records for last month( 30 days ago), when I add this condition in WHERE a syntax error occure , would you please help me?



ALTER PROCEDURE [dbo].[NewsTimeBroadCastTopicProc]
@VarChannel_ID int
AS
BEGIN
select NewsTimeBroadCast_ID, NewsTimeBroadCast.NewsTime_ID, Movie_URL, DateTimeSystem,LEFT(DatePakhsh,10) as DatePakhsh, DownloadCount,NewsTime.NewsTime_ID
,[Channel_ID]
,[TehranTime]
,[Name]
,[VienTime]
,[LondonTime]
,[NewyorkTime]
,[LosanjlesTime]
,[SidneyTime]
,[KoalalampoorTime]
,[EstabnulTime]
,[CentralAmericaTime]
,[TokyoTime]
,[DehliTime]
,[Record_Time]
from NewsTimeBroadCast inner join NewsTime
on NewsTimeBroadCast.NewsTime_ID=NewsTime.NewsTime_ID
where (( NewsTimeBroadCast.DateTimeSystem,-30, getdate()) AND ( NewsTime.Channel_ID=@VarChannel_ID order by NewsTimeBroadCast.DatePakhsh desc))


END
Post #1508645
Posted Saturday, October 26, 2013 4:18 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 15,662, Visits: 28,057
You've got the function in place, but it's not comparing it to anything. That's why you're getting an error. Assuming you want exactly 30 days ago, you can do this;
SELECT...
FROM...
WHERE NewsTimeBroadCast.DateTimeSystem = dateadd('d',-30,getdate())

But if you want all the entries from the last 30 days, do something like this:

SELECT...
FROM...
WHERE NewsTimeBroadCast.DateTimeSystem > dateadd('d',-30,getdate())

Where possible, avoid running functions against columns because that will prevent index use and lead to scans.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1508652
Posted Saturday, October 26, 2013 4:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, December 28, 2013 6:04 AM
Points: 107, Visits: 940
Thank you but again there is a error "invalid parameter 1 specified for dateadd!"
Post #1508655
Posted Saturday, October 26, 2013 5:12 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 15,662, Visits: 28,057
Oops. My bad. I typed it from memory. No quotes around the d. You can look up more about DATEADD here.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1508657
Posted Saturday, October 26, 2013 6:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, December 28, 2013 6:04 AM
Points: 107, Visits: 940
Thanks alot
Post #1508658
Posted Monday, October 28, 2013 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
I prefer to type the name of the datepart. It really helps avoid confusion and you don't have to resort to the documentation very often to figure out some of the lesser used dateparts.

dateadd(day, -30, getdate()) 



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1508891
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse