• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 796
  • Last Modified:

PROC SQL: Delete rows in which two date fields differ by more than 250 days

Hi all, I;m not familiar with PROC SQL (I think its similar to MS SQL)

I am running a statistics program called SAS and am trying to delete certain rows in a database. The fields 'Date_Announced' and 'Date' are the ones I am concerned with. I'm trying to subtract the two coloumns and then delete the rows for which the result is >250 . The sheet is called Asx.mergernew.

Can someone help me with the exact command to type into my command window?
0
hubfub
Asked:
hubfub
  • 4
  • 2
1 Solution
 
RiteshShahCommented:
you can do something like:

delete from YourTableName where DATEDIFF(dd,Date_Announced,[date])>=205
0
 
RiteshShahCommented:
if you want in stored procedure than use this one.




create proc DeleteRecord
as
begin
delete from YourTableName where DATEDIFF(dd,Date_Announced,[date])>=205
end
 
 
--run abov proc
exec deleteRecords

Open in new window

0
 
hubfubAuthor Commented:
HI RiteshShah

DATEDIFF is not a command that is recognised nor is it a field. Is there a way to get around this? One field in ours is Date and the other is Date_Announced. What is dd?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
RiteshShahCommented:
DATEDIFF is a date function built in in SQL Server, "DD" gives you difference in days.

can't you even run below command?



select DATEDIFF(dd,getdate()-250,getdate())

Open in new window

0
 
hubfubAuthor Commented:
When I run that command it says 'syntax error'

I am trying to run these commands in a statistics program called SAS although the command window says PROC SQL - is this why the commands are not running?
0
 
RiteshShahCommented:
those command should run in SQL Server, not familiar with PROC SQL.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now