October 26, 2013 at 2:13 am
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
October 26, 2013 at 4:18 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2013 at 4:58 am
Thank you but again there is a error "invalid parameter 1 specified for dateadd!"
October 26, 2013 at 5:12 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2013 at 6:00 am
Thanks alot
October 28, 2013 at 7:53 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply