how to change timestamp of a DB in SQL Server 2005?

[alexyxj]

Can anyone tell me how to change time for a DB in SQL Server 2005? I need to correct the time for a database, thanks.

[Wendelius]

You set the time of the windows server.

[alexyxj]

so you mean the database will use the time of windows server. my issue is for a specific database, say DB002, I wanna change its current time setting (set its current time 01-01-2006), can I do that?

[Wendelius]

SQL Server uses windows clock which then again uses clock from BIOS. So you can have only one time at the server which is the time of all databases. I don't know what happens if you set up a virtual PC. Since virtual PC has emulated bios it could be possible to have "several times" on one physical server. But... this is just a guess.

[alexyxj]

maybe we got into a wrong direction. let me clarify this issue. in one of my databases, I have some tables having timestamp columns, now I just wanna change displayed time for those columns. Can I just use some scripts like: update TABLE ** set **=getdate()

[Wendelius]

Sorry, I misunderstood you. Is the column's data type timestamp? Timestamp-typed columns cannot be modified via DML. Moreover they are not timestamps, but only unique identifiers that database engine update.

[alexyxj]

data type for the column is 'datetime', column name could be anything.

[Wendelius]

If it's datetime then you can simply set a desired date to the column:

UPDATE TableName SET DateTimeColumn = CONVERT(datetime, '01/01/2006', 101) ...

which will use midnight for time portion or you can use current date and subtract desired amount of days like:

UPDATE TableName SET DateTimeColumn = GETDATE() - 975 ...

which will today end up to 01/01/2006, but using current time FYI, if the column name would be timestamp, it is hardcoded to many libraries that is must have timestamp as data type. This will lead to problems in some cases (typically in cursor handling) if the actual datatype isn't timestamp.

[alexyxj]

THANKS Mika

[Wendelius]

You're welcome