Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Does anybody know of a way to alter a time zone on a SQL database?

Is there any tool that could find the objects using the date and time?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

The easiest way I have used to see if any objects are using a particular command (e.g. those that use a local time zone, such as GETDATE() and CURRENT_TIMESTAMP) is syscomments. The text column of this table in SQL 2000 has the actual command that makes up the object.

The caveat to this is if the procedure was created using WITH ENCRYPTION you will not be able to see or check the text column. I believe something like this should return what you are looking for:

SELECT OBJECT_NAME(ID), Text
FROM syscomments
WHERE Text LIKE '%GetDate%'
share|improve this answer
    
How is this at all the answer to the question? It appears completely unrelated. –  Matt Johnson Jul 26 '13 at 22:43
4  
To the attention of @MattJohnson, as well as all the others, who may be considering a downvote on this, the issue has been raised here: meta.dba.stackexchange.com/questions/1007/possible-bug-in-data –  Andriy M Jul 27 '13 at 12:33
    
@Matt I have updated both the question and this answer to make it clear why it answers the second part of the original question (the first part is simply to change the time zone in Windows, but the second part is to help identify those objects that that change might affect). –  Aaron Bertrand Jul 27 '13 at 20:28
2  
@AaronBertrand - Thanks, I understand now. I was thinking about the first part of the question, which is clearly not possible, but this answer does help with the second part. Thanks for clearing up the confusion –  Matt Johnson Jul 27 '13 at 22:18

Assuming SQL Server

  • getdate gives local server time
  • getutcdate gives utc / gmt

There is no timezone setting or changes possible inside SQL Server

share|improve this answer

To alter the database's timezone you might be able to use something like

ALTER DATABASE your-database-name SET TIMEZONE TO 'AST';

The syntax varies from vendor to vendor. Some platforms rely on the host operating system instead. To get platform-specific answers, tag your question with the dbms you use--PostgreSQL, SQL Server, Oracle, etc.

It's not clear what objects you want to find by date and time.

share|improve this answer
    
This is for website reservation system and trying to find out all the stored procedures that uses getdate() on the database. Are you saying the alter database t-sql will completely change the getdate() to use that timezone? –  db7 Aug 30 '11 at 11:56
1  
SQL Server doesn't support ALTER DATABASE ... SET TIMEZONE.... –  Mike Sherrill 'Cat Recall' Aug 30 '11 at 17:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.