Update new date in database
-
Hi all, just got to find an easier way, can any tell me if you can update the entire database with a new time in the timestamp field. i've got this project but the data is 14month old, it would be good if the data could be 3 or 4 days old just to debug or test a few things. Anyway, ive been using this:-
select dateadd(d,430,timestamp)from alarm_logthis brings back the original table plus 430 days. So how do i update this data back into the table, cause i have nothing in the table to generate a whereclause. secondly, once ive got one table updated... i could use sysobjects to find all the tables in the database and then loop through them...but how! - im sure i read somewhere that sql doesn't store a row number, there is no such thing, but surely there must be a way to interate through a table list? Cheers Andy -
Hi all, just got to find an easier way, can any tell me if you can update the entire database with a new time in the timestamp field. i've got this project but the data is 14month old, it would be good if the data could be 3 or 4 days old just to debug or test a few things. Anyway, ive been using this:-
select dateadd(d,430,timestamp)from alarm_logthis brings back the original table plus 430 days. So how do i update this data back into the table, cause i have nothing in the table to generate a whereclause. secondly, once ive got one table updated... i could use sysobjects to find all the tables in the database and then loop through them...but how! - im sure i read somewhere that sql doesn't store a row number, there is no such thing, but surely there must be a way to interate through a table list? Cheers Andy1. If you are updating ever row in the table you don't need a where clause
update alarm_log set timestamp = dateadd(d,430,timestamp)2. Use a cursor. See Google or help in Query Analyzer for more info.Mike Lasseter
-
Hi all, just got to find an easier way, can any tell me if you can update the entire database with a new time in the timestamp field. i've got this project but the data is 14month old, it would be good if the data could be 3 or 4 days old just to debug or test a few things. Anyway, ive been using this:-
select dateadd(d,430,timestamp)from alarm_logthis brings back the original table plus 430 days. So how do i update this data back into the table, cause i have nothing in the table to generate a whereclause. secondly, once ive got one table updated... i could use sysobjects to find all the tables in the database and then loop through them...but how! - im sure i read somewhere that sql doesn't store a row number, there is no such thing, but surely there must be a way to interate through a table list? Cheers AndyIt is a strange practice to name your field timestamp and then fill them with a datetime or smalldatetime data type. I'm assuming that your timestamp field is in fact a datetime or smalldatetime, because the timestamp data type is binary - and would not update without error using the dateadd as you show. Plus, the timestamp data type is updated automaticaly when you change the value of a field. Anyway, if your timestamp fields are in fact dates, this should work:
DECLARE @cSQL varchar(100)
DECLARE @cTableName varchar(100)SELECT @cSQL = ''
DECLARE TableNames CURSOR FOR
SELECT DISTINCT table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'timestamp'OPEN TableNames
FETCH NEXT FROM TableNames INTO @cTableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('UPDATE ' + @cTableName + ' SET timestamp = DATEADD(d,430,timestamp)')FETCH NEXT FROM TableNames INTO @cTableNameEND
CLOSE TableNames
DEALLOCATE TableNames--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters