SQL Server 2005 Date Localization

Question

I have a procedure that defaults todays date if no parameter is passed into it.

CREATE Procedure GetDirectoryRenewalReminders    
(@DateToday datetime = Null)  
As     
If @DateToday Is Null   
Begin  
 Set @DateToday = GetDate()  
End  
...
Go

How can I ensure that the GetDate() value will default to the current UK time when called from an application in the UK, and a Australian time when called from an applciation in Australia?

Answer 1

You can use GETUTCDATE() to the the time in utc from the server. Then you can have the client add the local time to that.

Answer 2

The client application should apply the translation for the client location into a standard datetime for SQL Server... but you'll have to apply the timezone offset to make it GMT in the client.

Then compare against GETUTCDATE()

FYI: GMT is UTC if you're British ;-)

Answer 3

It will always return local server time. There is no way for the database server to know where the user 'is' time-zone-wise. You could pass in a timezone offset to the procedure.