Dates in SQL Server consistently off by 12 hours

Question

Our production platform technologies are these: PHP, MS SQL Sever, and IIS.

PHP is set to the timezone 'Pacific/Auckland' which is current +12:00 GMT/UTC.

When we retrieve dates from SQL Server, they are consistently 12 hours 'behind' what they should be, ie. SQL Server is storing and serving them as GMT dates, even though the time zone on the server itself is Pacific/Auckland too.

Is there a hidden sp function some where you can use to set the timezone? If this is not timezone related, please enlighten me!

Answer 1

Can you open up sql server management studio and type "select getdate()" to make sure it is sql server that is wrong?

Are you using getdate() or another sql function to get the date?

Answer 2

If your statement is both accurate and general, then you should be able to reproduce this simply by inserting a row into a table with a datetime column like this:

CREATE TABLE OffBy12 (
 IsThisOff datetime not null
)
GO

INSERT INTO OffBy12 (IsThisOff) VALUES ('2009-06-28')

SELECT IsThisOff
FROM OffBy12

If you're right, then the time portion of the date will not be 00:00.

If you're not correct, then this must be something more complex. In that case, you may want to say how the data are getting into SQL Server.