[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Update date/time in all the tables in DB

Sehboo

3/16/2007 1:54:00 PM

I have about 100+ tables in my DB, and lot of them have date/time
fields. I have to update all these fields. I just need to add 5
hours to all the datetime fileds (to make them UTC time, instead of
local). I am on SQL Server 2000.

Is there any easy solution for this, or will I have to write cursors
for each table?

thanks
4 Answers

--CELKO--

3/16/2007 2:56:00 PM

0

>> I have about 100+ tables in my DB, and lot of them have date/time
fields [sic]. I have to update all these fields [sic]. I just need
to add 5 hours to all the datetime fileds [sic] (to make them UTC
time, instead of local). Is there any easy solution for this, or will
I have to write cursors for each table? <<

Just write an UPDATE statement for each table, not a cursor. You can
get the table and column names from the schema information tables and
use a text editor to generate your code. This should be about a day's
work, sicne the time stamps probably do not reference anything else.

Tony Rogerson

3/16/2007 3:52:00 PM

0

UPDATE <tablename>
SET <dbcol> = DATEADD( hour, 5, <dbcol> )

Tony.


"Sehboo" <MasoodAdnan@gmail.com> wrote in message
news:1174053255.336225.59470@e1g2000hsg.googlegroups.com...
>I have about 100+ tables in my DB, and lot of them have date/time
> fields. I have to update all these fields. I just need to add 5
> hours to all the datetime fileds (to make them UTC time, instead of
> local). I am on SQL Server 2000.
>
> Is there any easy solution for this, or will I have to write cursors
> for each table?
>
> thanks
>

Tom Cooper

3/16/2007 9:15:00 PM

0

Before doing the update, make sure that what you want to do is add the same
number of hours to every datetime. Unless you never go on daylight savings
time, the number of hours you need to add to local time for a given date
will vary depending on whether or not you are on daylight savings time on
that date.

Tom

"Tony Rogerson" <tonyrogerson@torver.net> wrote in message
news:3438266E-1405-43AC-A871-6DE42096A07C@microsoft.com...
> UPDATE <tablename>
> SET <dbcol> = DATEADD( hour, 5, <dbcol> )
>
> Tony.
>
>
> "Sehboo" <MasoodAdnan@gmail.com> wrote in message
> news:1174053255.336225.59470@e1g2000hsg.googlegroups.com...
>>I have about 100+ tables in my DB, and lot of them have date/time
>> fields. I have to update all these fields. I just need to add 5
>> hours to all the datetime fileds (to make them UTC time, instead of
>> local). I am on SQL Server 2000.
>>
>> Is there any easy solution for this, or will I have to write cursors
>> for each table?
>>
>> thanks
>>
>


Mr Tea

3/16/2007 11:13:00 PM

0

This might save you an hour or two:

select 'update '+quotename(object_name(syscolumns.id))+' set
'+quotename(syscolumns.name)+' =
dateadd(hour,5,'+quotename(syscolumns.name)+')'
from syscolumns
join sysobjects
on sysobjects.id=syscolumns.id
where syscolumns.xtype in (61,58)
and sysobjects.type='U'

Regards
Mr Tea

"Sehboo" <MasoodAdnan@gmail.com> wrote in message
news:1174053255.336225.59470@e1g2000hsg.googlegroups.com...
>I have about 100+ tables in my DB, and lot of them have date/time
> fields. I have to update all these fields. I just need to add 5
> hours to all the datetime fileds (to make them UTC time, instead of
> local). I am on SQL Server 2000.
>
> Is there any easy solution for this, or will I have to write cursors
> for each table?
>
> thanks
>