just

Question

just

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
April 4, 2009 at 3:40 am

#129145

If we need to know by using select getdate()
but
Can we change a Server(machine) datetime in sql_server...?
is it Possible?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Not using T-SQL. To do that you'd need to use xp_cmdshell and issue OS commands

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 2

But, I don't know OS command like how to change the Date time of server machine...

do u have a IDEA?

Please send that

Lowell SSC Guru Points: 323518 More actions · Answer 3

saravanantvr1984 (4/4/2009)
But, I don't know OS command like how to change the Date time of server machine...
do u have a IDEA?
Please send that

start>>Run cmd to get a command prompt:

time

it states the current time and a prompt to enter the new time:

Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.
C:\Documents and Settings\Administrator>tim
The current time is: 21:10:32.82
Enter the new time:
C:\Documents and Settings\Administrator>

same thing happens if you type date in the command window.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I do know, but I'm curious. Why do you want to set the server's time through SQL? That should be something that's done maybe once, by the server admin. If the server's using internet or domain time server, it should never be necessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 5

I'm admittedly slow at times, but i just changed the time on my server to 12 hours earlier than the real time..., and compared getdate() to OS time; they are identical, or within the milliseconds it takes for each command to return; which is what i would expect, since SQL server would not have a separate clock function, it just gets it from the OS anyway.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

If we need to know by using select getdate()

I hope you're not trying to change the system time to retrieve rows that had the current getdate() value stored when they were created. It's totally unnecessary and will cause problems with the rows being inserted after you have changed the date. If you are trying to change the date BECAUSE rows are being timestamped with getdate() then shame on you.

Why are you trying to do this again?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Vladan SSC-Insane Points: 22072 More actions · Answer 7

Most often such question (or even request to do it) arises when there are some problems with date of certain action - especially if something needs to be entered into bookkeeping into previous month/year, and the system automatically uses date when certain operation was performed. I got the same request last January... "Can you just set the date at 31.12.2008 for a day or two until we have processed everything?".

The correct answer to such requests is "No, it can't be done". IMHO it is one of the things where you (as DBA) should be absolutely inflexible. There are always other ways how to solve the problem, and although they may mean additional manual work to users (or programming to you), my opinion is that setting different system date should not be considered at all.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

I quite agree, Vladan. "Backdating" transactions is often a questionable business practice. Although there may be disastrous situations where recovery fails and transactions MUST be entered after the fact, there should always be some audit trail to establish exactly which transactions were backdated. This could be a log, an archive, or [TransactionDate] columns which exist independently of [InsertDate] columns.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills