data purging - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

data purging

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 03:39:49

if my table name is table1 the it should execute SQL query as

select * from table 1 where date_field =25-may-2009 and then purge all the data retrived from this query from table1

like there are total 25 tables for this....

can anybody has suggestions for this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 03:48:12

Does all the 25 tables have same date_field colum name?

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 04:08:41

nope it dependes upon that perticular table's column name

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 07:21:48

any help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-26 : 07:28:36

quote:
Originally posted by qutesanju

any help

If the column names are different in each table, use 25 different queries to delete data

Madhivanan

Failing to plan is Planning to fail

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 07:35:56

ok i created a procedure like this........

alter Procedure [DataPurge]
( @TableName as VarChar(100) ,
@LastRunDate as datetime
)
AS
begin

Declare @SQL VarChar(1000)
declare @LastRunDate1 datetime

SET @LastRunDate1 =CONVERT(DATETIME,@LastRunDate)
print @LastRunDate
print @LastRunDate1

SELECT @SQL ='SELECT * FROM ' + @TableName + ' where CONVERT(DATETIME,PRODU_DATE_DT)<= '+ @LastRunDate1
print @SQL
--Exec ( @SQL)
end

but i m getting error while executing this procedure as .........

Conversion failed when converting datetime from character string.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 08:02:11

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 09:03:42

CONVERT(DATETIME,PRODU_DATE_DT) <= ' + QUOTENAME(CONVERT(VARCHAR(20), @LastRunDate1, 121), '''')

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 09:37:20

when i executed SP then in that case it's still giving error ........Must declare the scalar variable "@LastRunDate1".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-26 : 09:44:15

What does print @SQL result?

Madhivanan

Failing to plan is Planning to fail

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 09:45:22

Print @SQL returns..........
SELECT * FROM KC_PROD_SUM where CONVERT(DATETIME,KC_PROD_SUM_MES_PRODU_DATE_DT)<= + QUOTENAME(CONVERT(VARCHAR(20), @LastRunDate, 121), '')

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 09:54:04

while executing SP..it should not give an error ...........Must declare the scalar variable "@LastRunDate1"....coz LastRunDate1 is already declared

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-26 : 09:54:35

Post the full code you used

Madhivanan

Failing to plan is Planning to fail

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 10:02:15

alter Procedure [DataPurge]
( @TableName as VarChar(100) ,
@LastRunDate as datetime
)
AS
begin

Declare @SQL VarChar(1000)

SELECT @SQL ='SELECT * FROM ' + @TableName + ' where CONVERT(DATETIME,PRODU_DATE_DT)<= '+ QUOTENAME(CONVERT(VARCHAR(20), @LastRunDate, 121), '''')

print @SQL
Exec ( @SQL)

end

now with this code .it gives..........correct PRINT @ SQL as......
SELECT * FROM PROD_SUM where CONVERT(DATETIME,KC_PROD_SUM_MES_PRODU_DATE_DT)<= '2009-05-25 00:00:00.'.......
but SP fails to execute while Exec ( @SQL)
saying an error as......Conversion failed when converting datetime from character string.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 10:04:32

It means you have values in column KC_PROD_SUM_MES_PRODU_DATE_DT which cannot be converted into datetime!
What datetype is KC_PROD_SUM_MES_PRODU_DATE_DT column?

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 10:15:42

it's having datetime datatype

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 10:17:24

If KC_PROD_SUM_MES_PRODU_DATE_DT already is datetime, there is no need to convert it to datetime again, right?

SET @SQL ='SELECT * FROM ' + @TableName + ' where PRODU_DATE_DT <= ' + QUOTENAME(CONVERT(VARCHAR(20), @LastRunDate, 121), '''')

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-05-26 : 10:28:44

i tried..i got an error.....Conversion failed when converting datetime from character string.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 10:30:21

What datatype is variable @LastRunDate?

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts