How do I add 1 hour to datetime SQL column data?

Question

I used this part of a query to create a table column for the date and time a row is added:

order_date datetime NOT NULL DEFAULT GETDATE()

and whenever a new row is created, the data for order_date is set to something like this:

Apr 8 2014 9:52AM

For some reason, when a row is created and the order_date column data is set, the hour is set 1 hour back. For example, the above column data for Apr 8 2014 9:52AM was set at 10:52AM.

Is there a way to set it 1 hour ahead so that it is correct with my current time?

Thank you for any help. All help is greatly appreciated.

Answer 1

Use DATEADD()

DATEADD(hh, 1, order_date)

EDIT:

If the time is being set an hour back, you may have a wrong system time. So, it would be better if you just ask server admin to correct it.

Answer 2

You should consider using DATETIMEOFFSET as your daatype instead of DATETIME.

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

You can use it with SYSDATETIMEOFFSET().

Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

Example:

 CREATE TABLE DateTest (id INT, order_date DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET())
 INSERT INTO DateTest (id) VALUES (1)
 SELECT * FROM DateTest