Date Configuration Per Database Instance

[olutimi]

Please advice.

How do you configure dates per country on database instances.
I want to configure the database instance to reflect dates based on thier country. I have four European Databases in different time zones on the same server.

Thanks. I have SQL Servr 2000 SP3A running on WIN2k SP4.

Help appreciated.

[MAK]

getdate() function uses system date and time.

You have to create your own function on every database to get current date and time for the time zone. using UTC time is the right way.

Here is an example. for the database DB1 it would add 6 hrs to the current datatime and for the database DB2 it would subtract 1 hr. you will get the idea.

use master
go

EXEC sp_serveroption 'Yourservername', 'DATA ACCESS', TRUE
go


use DB1
go
CREATE FUNCTION dbo.getdate2()
RETURNS DATETIME
AS
BEGIN
DECLARE @dt DATETIME
SELECT @dt = dt
FROM OPENQUERY
(
testvm2,
'SELECT dt = GETDATE()'
)

RETURN dateadd(hh,6,@dt )
END
GO
select dbo.getdate2()

use DB2
go
CREATE FUNCTION dbo.getdate2()
RETURNS DATETIME
AS
BEGIN
DECLARE @dt DATETIME
SELECT @dt = dt
FROM OPENQUERY
(
testvm2,
'SELECT dt = GETDATE()'
)

RETURN dateadd(hh,-1,@dt )
END
GO
select dbo.getdate2()