SQL add 2 hour to a column with type "date"

Question

I had a column with dates, in an Select statement, and i want to add 2 h to each row in this Select.

Any advice?

Seems you are asking about SQL, could you [edit] your post and tag the DBMS? — Prisoner, Nov 07 '16 at 06:46
the DBMS I mentioned is something like 'SQL Server', 'MS SQL', 'Oracle', 'MySQL'... etc — Prisoner, Nov 07 '16 at 06:48
Could you confirm you are using sql-server? From the comment you leave in Aravind's answer, it seems you are using Oracle — Prisoner, Nov 07 '16 at 06:58

score 5 · Accepted Answer · answered Nov 07 '16 at 07:12

I prefer using interval for this:

select deliverydate + interval '2' hour
from the_table;

The above is standard SQL and works well in Oracle.

score 2 · Answer 2 · edited Nov 07 '16 at 07:09

2

MS Access Update Query:

UPDATE Table1 AS t2 INNER JOIN Table1 ON t2.ID = Table1.ID 
SET Table1.DeliveryDate = DateAdd("h",2,[t2].[DeliveryDate]);

UPDATE Table1 AS t2 INNER JOIN Table1 ON t2.ID = Table1.ID 
SET Table1.DeliveryDate = DateAdd("hh",2,[t2].[DeliveryDate]);

SELECT DateAdd("h",2,Table1.DeliveryDate) AS NewDateTime
FROM Table1;

SELECT DateAdd("hh",2,Table1.DeliveryDate) AS NewDateTime
FROM Table1;

SELECT (Table1.DeliveryDate + 2/24) AS NewDateTime
FROM Table1;

edited Nov 07 '16 at 07:09

Prisoner

answered Nov 07 '16 at 06:48

i don't want to update my table – BOB Nov 07 '16 at 06:51
See my updated answer. You just need to create a calculated column: In this example : `DateAdd("hh",2,[OldDate]) AS NewDateTime` `NewDateTime` is the field alias/name for a field that is 2 hours greater than the `[OldDate]` fields. You may need to create some additional logic to handle Nulls. – Nov 07 '16 at 06:59
it doesn't work for me, i get error , "DateAdd": invalid identifier – BOB Nov 07 '16 at 06:59
I didn't see that you are using Oracle. I update my answer. – Nov 07 '16 at 07:07
Thank you, it works also with your last query. But unfortunately i can't accept 2 answers – BOB Nov 07 '16 at 07:25
No worries. Happy Coding!! – Nov 07 '16 at 07:30

Aravind Pillai · Answer 3 · 2016-11-07T06:39:52.913

1

CREATE TABLE testtime (my_date datetime);
  INSERT INTo testtime VALUES (GETDATE());

this prints the actual date

  select * from testtime

this prints time + 2 hrs

  select DATEADD(HOUR, 2, my_date) from testtime

edited Nov 07 '16 at 06:39

answered Nov 07 '16 at 06:22

Aravind Pillai

if I did that it will change the data in database, all I want is to add 2 H into `select` – BOB Nov 07 '16 at 06:27
yea that was a mistake. sorry for that. Please do refer that link for ur answer ... – Aravind Pillai Nov 07 '16 at 06:30
"dateadd": invalid identifier – BOB Nov 07 '16 at 06:35
@Marius : try this out its working for me. – Aravind Pillai Nov 07 '16 at 06:42
unfortunetly is not working for me, i don't know why...my column with dates is type `date` i can't make `datetime` – BOB Nov 07 '16 at 06:46
@Marius, you said the datatype is `date`, may I know why you need to add 2H? The result will always be `yyyy/mm/dd 02:00`? – Prisoner Nov 07 '16 at 06:53
into my `select` i add `to_char(time_from_UTC, 'dd.mm.yyyy hh24:mi')` to show the datetime – BOB Nov 07 '16 at 06:56
yeah , that is a point.. change ur type to datetime from date. Try after that. Its working fine for me nyway.. – Aravind Pillai Nov 07 '16 at 06:56
I don't have acces to change the datebase. Only i need to do is to work with `select` – BOB Nov 07 '16 at 06:58
@marius , can u tell me what is the problem with below code which suggested by Aravind R Pillai ? select DATEADD(HOUR, 2, my_date) from testtime – Ronak Patel Nov 07 '16 at 07:05
@Ronak Patel, "dateadd": invalid identifier – BOB Nov 07 '16 at 07:07
are u using MS SQL Server ? – Ronak Patel Nov 07 '16 at 07:07
@RonakPatel, check the new comment in question, he using oracle – Prisoner Nov 07 '16 at 07:09