Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SQL SERVER TIMEZONE PROBLEM

Author  Topic 

meetabhilash
Starting Member

10 Posts

Posted - 2008-03-28 : 08:09:35
Can i able to find what time zone that the os has been set,
using some sql function

or is it better to use an sql script

like calculating the difference between GetUtcDate() and GetDate()

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-28 : 08:24:32
Are you talking about GetDate() function? Is this problem happening only for the for the database you restored? What is the OS timezone settings on Remote server?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-29 : 00:10:34
Getdate() gets current date and time from OS.
Go to Top of Page

meetabhilash
Starting Member

10 Posts

Posted - 2008-03-31 : 02:05:58
quote:
Originally posted by harsh_athalye

Are you talking about GetDate() function? Is this problem happening only for the for the database you restored? What is the OS timezone settings on Remote server?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"



oops sorry , yeah its GetDate() function
yes.it is only happening for the db tha i have restored;

the OS timezone settings on Remote server is : (GMT + 12:00) Auckland,Wellington


coding is my passion
Go to Top of Page

meetabhilash
Starting Member

10 Posts

Posted - 2008-03-31 : 04:04:27
HI everyone,

How can we set the Time Zone of a database permenently.

It should work regardless of OS Time Zone.

coding is my passion
Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-31 : 05:06:23
how about using getutcdate() instead?

Em
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-31 : 22:41:48
No, can't set time zone at db level.
Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-02 : 03:05:52
quote:
Originally posted by meetabhilash

Can i able to find what time zone that the os has been set,
using some sql function

or is it better to use an sql script

like calculating the difference between GetUtcDate() and GetDate()


hmm... when you edit your original post so that it's a different question altogether, the replies don't make much sense anymore you should just add it as a reply or start a new post if it's really that different

in answer to THIS question though, i would be calculating the offset between getutcdate() and getdate()



Em
Go to Top of Page

meetabhilash
Starting Member

10 Posts

Posted - 2008-04-02 : 05:03:55
quote:
Originally posted by elancaster

quote:
Originally posted by meetabhilash

Can i able to find what time zone that the os has been set,
using some sql function

or is it better to use an sql script

like calculating the difference between GetUtcDate() and GetDate()


hmm... when you edit your original post so that it's a different question altogether, the replies don't make much sense anymore you should just add it as a reply or start a new post if it's really that different

in answer to THIS question though, i would be calculating the offset between getutcdate() and getdate()



Em



oops... iam really sorry.

iam a newbie in forum posting.i dont know much more about how to post.

anyway thanks for your replay.


coding is my passion
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-02 : 23:13:55
You can use datediff like datediff(hh, GetUtcDate(), GetDate()).
Go to Top of Page

meetabhilash
Starting Member

10 Posts

Posted - 2008-04-03 : 00:11:23
Can i able to write a function
GetDate() inside the sql scalar-valued function so that all my call to the GetDate() inside my SP will redirect to this local function.


coding is my passion
Go to Top of Page

meetabhilash
Starting Member

10 Posts

Posted - 2008-04-03 : 00:16:42
How can i add say 5 hours to GetutcDate()

when i add 5 to getutcdate() 5 days has been incremented.

coding is my passion
Go to Top of Page

meetabhilash
Starting Member

10 Posts

Posted - 2008-04-03 : 00:20:16
Yeah , i got it.

but i have to call the local getdate() like this

dbo.getdate()

is there anyway to avoid this dbo.

coding is my passion
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-03 : 14:04:01
You seem pretty good about answering your questions, The problem is I don't really know what you're asking. What do you mean by "is there anyway to avoid this dbo."? The following will add 5 hours.

select dateadd(hh,5,GETUTCDATE()) --hh represents hours, 5 is what's being added to hours
select dateadd(hh,5,GETDATE()) - same as above but for your current system date/time

Terry
Go to Top of Page

meetabhilash
Starting Member

10 Posts

Posted - 2008-04-04 : 00:02:10
ok.
i have a lot oF SP's inside my db and there are lots of Getdate() calls.

i want to redirect all that GetDate() call to my local GetDate() function that i have writtern inside my DB.

But now it only work when i change the GetDate() to dbo.GetDate() to work the local function otherwise it will take the SQL GetDate().

Is there anyway to overcome this.

coding is my passion
Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 03:05:21
when you call a user defined function you have to specify the owner as well i.e... dbo.YourFunction


Em
Go to Top of Page
   

- Advertisement -