Add days to all datetime columns in the database

Hi,
I got a request from our Sr. Director regarding demo data.
He's stated that demo databases tend to age with time. As
you enter orders they become older and older as time
passes.
Anyways, he's asking how difficult would it be to write a
SQL utility that asks the user for a number of days and
then ran through the ENTIRE database looking for EVERY
Date/Time column and add the number of days to the data
content of the field.
I've sort of created already somewhat a DML command that
will autogenerate for me the update statement, but is
there a way for me to prompt the user in Query Analyzer
for a value to set the variable? Does it have to be done
via command prompt?
I've copied below my DML statement that I've quickly
written up.
select 'update ' + OBJECT_NAME(id) + ' set ' + substring
(name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
OBJECT_NAME(id)
from syscolumns
where name like '%Date%'
order by OBJECT_NAME(id)
TIA,
BettinaYou can make a template in Query Analyzer ...
You can insert tags like: <myTag, varchar(20), 'abc'> (Structure is:
<TagName, DataType, Default>)
Then just hit CTRL-SHIFT-M and it will prompt for replacement. Very handy.
You can also save your template in the <SQL Server
Root>\80\Tools\Templates\SQL Query Analyzer\ folder, as a .TQL file, and
when you click on the Templates tag in Query Analyzer it will be there for
you to use.
Or you could just make a stored procedure...
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>|||In the databases I see you will pick up lots of things that don't correspond
to an SQL datetime data type.
If you get this syscolumns.xtype contain they system type of the column.
syscolumns.xtype in (58, 61) will pick all datetime columns.
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>