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.

Check the system time on your SQL Server. Fix that, not the symptom. — MatBailie, Apr 08 '14 at 17:26
I can't, because it is not my server, therefore I cannot change the timezone. — Kelsey, Apr 08 '14 at 17:27
@MatBailie got this one. Check your system time. The insert works off of that and if your timezone is off, it'll set it to the wrong timezone. — crthompson, Apr 08 '14 at 17:27
@Kelsey - And if they fix the time after you put in place your fix? — MatBailie, Apr 08 '14 at 17:28
If you are working in an environment like that you may want to consider `getutcdate()` instead since everything will be off and you will need to adjust everywhere. — Jeremy Cook, Apr 08 '14 at 17:29

score 38 · Answer 1 · answered Apr 08 '14 at 17:26

38

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.

answered Apr 08 '14 at 17:26

Nathan

1,220
3
15
26

How would I use this in a query? (DATEADD(hh, 1, order_date)) – Kelsey Apr 08 '14 at 17:37
INSERT INTO table_name VALUES (DATEADD(hh, 1, order_date)). You supply the rest of the columns. – Nathan Apr 08 '14 at 17:39
This is what I am using right now and it doesn't seem to be inserting any new rows: `$sql5="INSERT INTO tracking_orders (account_id, order_date) VALUES ('".$acc_id."',' DATEADD(hh, 1, order_date)')"` – Kelsey Apr 08 '14 at 17:54
Are you sure you're using SQL SERVER? – Nathan Apr 08 '14 at 17:58
Yes, I am using `MSSQL`. – Kelsey Apr 08 '14 at 17:58
But, I am running the query on my PHP page. – Kelsey Apr 08 '14 at 17:59
You can't use that method in PHP. What you can do is create a Stored Procedure in MSSQL, put the DATEADD() method there and then call the SP in PHP. – Nathan Apr 08 '14 at 18:24
I don't have access to the CPanel, so I have to do everything through PHP, unfortunately. – Kelsey Apr 08 '14 at 18:41
You can use something like: update Test set YourDate = DATEADD(hh, 1, order_date) where IdTest = 1 – Lucas Prestes May 30 '19 at 14:05

Karl Kieninger · Accepted Answer · 2014-04-08T19:06:46.253

5

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

edited Apr 08 '14 at 19:06

answered Apr 08 '14 at 17:31

Karl Kieninger

8,841
2
33
49

I am using `date_default_timezone_set('America/Edmonton');` on my PHP page so I hope it will set the date/time to that. How would I go about creating a query to create a table column for this type of data? – Kelsey Apr 08 '14 at 18:48
SQL Server DATETIME doesn't have Time Zone info in it so PHP cannot do anything with it without more info. I'll might not have to time to add the query, but it's basically the same just using the different type and different function. – Karl Kieninger Apr 08 '14 at 19:02
Very good answer, here is an up vote. I would appreciate an up vote on my question, if possible. – Kelsey Apr 08 '14 at 19:06

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

2 Answers2