Are there any problems rounding up dates like this

DECLARE @FiveDaysPrevious  DATETIME
SET @FiveDaysPrevious = '12/12/2006 2:12:12'
SET @FiveDaysPrevious = DATEADD( HOUR, 23-datepart(
HOUR,@FiveDaysPrevious ), @FiveDaysPrevious )
SET @FiveDaysPrevious = DATEADD( MINUTE, 59-datepart(
MINUTE,@FiveDaysPrevious ), @FiveDaysPrevious )
SET @FiveDaysPrevious = DATEADD( SECOND, 59-datepart(
SECOND,@FiveDaysPrevious ), @FiveDaysPrevious )

Wil

(Sn0tt***@yahoo.co.uk) writes:
Don't really know what you want to achieve, but this appears simpler:

   dateadd(minute, -1, dateadd(day, 1, convert(char(8), @d, 112)))

Also, beware that a dateformat like the you use is subject to different
interpretations depending on language and datetime settings. See
http://www.karaszi.com/SQLServer/info_datetime.asp.

Erland Sommarskog wrote:

I was trying to round up to the end of the day,

I wanted to avoid coverting in to a string format to try and avoid
language and datetime settings.

(Sn0tt***@yahoo.co.uk) writes:
In that case my code should read:

  dateadd(ms, -3,

Note the funky -3. In practice it would be better to write:

  WHERE datecol >= @fromdate
   AND  datecol < dateadd(day, 1, convert(char(8), @d, 112))

That's why I used 112. The format YYYYMMDD is a safe format.

And has been demonstrated many times, it's at least as safe, and on top of
that more efficient, to avoid converting to a string and back in the first
place.  Using DATEDIFF is a better approach than string truncation.  IMHO.

A

Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:
Maybe. But I looked the expression you posted and asked myself: what is
that doing?

Of course, if you do it often enough, you know that after a while. But
I've been converting to style 112 for 15 years, so *I* know what that
one is doing.

That seems like a lot of unnecessary steps.  Much easier to go to the exact
day boundary at midnight than to try to take a second or a millisecond off
of midnight.  Then change BETWEEN to two separate clauses (>= and <).

DECLARE @FiveDaysPrevious SMALLDATETIME;
SET @FiveDaysPrevious = '20061212 2:12:12';

SET @FiveDaysPrevious = DATEDIFF(DAY, -1, @FiveDaysPrevious);
SELECT @FiveDaysPrevious;

In addition to not trying to find the last possible datetime value in a
calendar day, I strongly recommend avoiding ambiguous formats like m/d/y or,
to illustrate the problem, is that d/m/y?  I have no idea, and neither will
SQL Server if you move your code from a US-based machine to one in the UK,
or change the SET DATEFORMAT or SET LANGUAGE settings.

A




<Sn0tt***@yahoo.co.uk> wrote in message
Show quoteHide quote

TOP 10 for each group
UDF that take a UDT as a parameter (CLR)
Table reaching max value of identity very early
Crosstab, Pivot?
Visual Basic 6.0 vs .NET Framework