Most concise way to write this query to add hour to a date

Question

I have a field with a DateTime. I want to ignore the time on that field and add a specified hour.

Here is an example of what I'm looking for, where @h could be any value from 0 to 23. (And I'm using getdate() instead of the field from my table for simplicity here).

declare @h int = 8
select cast(cast(cast(getdate() as date) as nvarchar(50)) + ' ' + CAST(@h as nvarchar(2)) + ':00' as datetime)

How can I write this more simply? Keeping in mind that I'm more interested in readability/maintainability than speed here.

Which version of SQL Server? 2005 or 2008? – DOK Jan 18 '12 at 18:30 — DOK, Jan 18 '12 at 18:30
Has to be 2008 if he is using `DATE` datatype... – JNK Jan 18 '12 at 18:33 — JNK, Jan 18 '12 at 18:33

score 6 · Accepted Answer · answered Jan 18 '12 at 18:33

6

SELECT DATEADD(hour, 1, CAST((CAST (GETDATE() as DATE)) as datetime))

You can put a parameter in place of the 1 here as needed.

answered Jan 18 '12 at 18:33

JNK

63,321
15
122
138

Crazy... I actually tried that first (I thought) but got an error that made it sound like DATEADD didn't support adding hours. Your statement clearly works though so +1. – Brandon Moore Jan 18 '12 at 18:35
You have to convert to `DATETIME` before adding. `DATE` datatype doesn't support hours. – JNK Jan 18 '12 at 18:36
Ah. Actually I did try that too, but I had the arguments out of order and forgot to cast again when I fixed that part. Wednesdays are basically my Mondays though so I blame it on that. – Brandon Moore Jan 18 '12 at 18:45

score 2 · Answer 2 · answered Jan 18 '12 at 18:35

2

How about this?

declare @h int = 8
select dateadd(hh, @h, cast(cast(getdate() as date) as datetime))

answered Jan 18 '12 at 18:35

Peter

12,541
3
34
39

@JNK Hey, maybe he's just a slow typer. I'll give him the benefit of the doubt :) – Brandon Moore Jan 18 '12 at 18:40

Most concise way to write this query to add hour to a date

2 Answers2

Linked