ansaurus

Question

How do I add Hours, mins, seconds to dateadd sql?

Answer 1

+3 A:

Try this:

DATEADD(second, -1, DATEADD(DAY, 1,"7/7/2010 12:00:00"))

Byron Whitlock 2010-07-07 22:29:24

Hmm I can't have it hard coded of course. So I took what you had and made this it seems to work but then again I don't know what I really didDATEADD(dd, 0, DATEDIFF(dd, 0, GETUTCDATE())) AND DATEADD(ss, -1, DATEDIFF(dd, -1, GETUTCDATE())

chobo2 2010-07-07 22:35:31

@chobo2: There's a tiny chance of a race condition with the code in your comment - if the date ticks over midnight between the first statement and the second then you'll end up with a range that covers two days rather than one.

LukeH 2010-07-07 22:44:16

Answer 2

+1 A:

DECLARE @start DATETIME
DECLARE @end DATETIME

SET @start = DATEADD(dd, 0, DATEDIFF(dd, 0, GETUTCDATE()))
SET @end = DATEADD(dd, 1, DATEADD(ms, -3, @start))

LukeH 2010-07-07 22:41:59

fancy bastard using millisecond precision ;) But really, good clear answer +1

Byron Whitlock 2010-07-07 22:43:23

Is this better? Will it encounter this race condition?

chobo2 2010-07-08 00:16:36

@chobo2: This calculates `@end` in relation to `@start` rather than by calling `GETUTCDATE()` a second time, so it's not vulnerable to the race condition. In the (unlikely) event that the date ticks over midnight between the two statements then the range will still be a single day.

LukeH 2010-07-08 06:09:44

ansaurus

tags:

views:

answers:

How do I add Hours, mins, seconds to dateadd sql?

related questions