ADD time 23:59:59.999 to end date for between

Question

I have been having an issue with using the following:

Column_Name BETWEEN @StartDate AND @EndDate. 

This is because the @EndDate = 00:00:00.000 for the time, which doesn't pick up all the values for that day.

How would I convert the @EndDate (Always 00:00:00.000) to always be Date + 23:59:59.999?

Brahma

Answer 1

One option that avoids needing to add EndDate + 23:59:59.999 is to not use the between comparison and instead use column_name >= @StartDate and column_name < @EndDate +1

Breda

+1 EndDate + 23:59:59.999 would get rounded up to the next day anyway. It would need to be EndDate + 23:59:59.997 to avoid that. – Said

I still would go with `and column_name < DATEADD(Day, 1,@EndDate) to avoid implicit casting. – Undeceive

< @EndDate +1 should result in values matching for EndDate through EndDate + 23:59:59.999. – Breda

I read that microsoft is considering to prevent datetime + 1 in a later sql version. A true brainfart from microsoft that will never happen. If anyone can show me that the implicit casting will slow the performance i will accept Wayne's explanation, otherwise i will always pick the datetime + 1 version. – Castellano

I gave it a +1, but don't do "@EndDate + 1". Use dateadd to be explicit about what unit you're adding to the datetime. – Tiepolo

Remember, implicit casting is a performance issue, it is a "what I wanted" issue. If for some reason, your @enddate variable was as varchar instead of a datetime it would not work as expected. The rule I was taught was to NEVER rely on implicit conversions. – Undeceive

Answer 2

Please note the accuracy and rounding of the DATETIME type in SQL Server 2005:

datetime values are rounded to increments of .000, .003, or .007 seconds

SQL Server 2008 introduced the DATETIME2 type which has an accuracy of 100 nanoseconds. So in SQL Server 2008 you could do:

DECLARE @d DATETIME = '2011-10-07 00:00:00.000'
SELECT DATEADD(MS, -1, DATEADD(D, 1, CONVERT(DATETIME2, @d)))

Alternatively you may want to avoid the BETWEEN operator in this case:

@StartDate <= Column_Name AND Column_Name < DATEADD(D, 1, @EndDate)

Tertial

Tom wouldn't the second example be: Column_Name >= @StartDate AND Column_Name < DATEADD(D, 1, @EndDate) – Clinker

@StartDate <= Column_Name is the same as Column_Name >= @StartDate – Tertial

Answer 3

You can change the time in a date like this (I'm using getdate() as an example):

select cast(convert(char(8), getdate(), 112) + ' 23:59:59.99' as datetime)

Explanation:

convert(char(8), getdate(), 112) converts the date to yyyymmdd format (as string).

Then you can just append the desired time, and convert the whole string to datetime again.

---

EDIT:

It slows the performance when you do the casting on a database column, yes.
But he has a datetime variable and he just uses the casting to change the time in the variable once
--> I see no performance issue if he uses my code to change his @EndDate variable.

Valid point, however. Casting is not a good solution in all situations.

Pentha

casting between datetime and varchar really slows the performance. You should try to avoid that unless it is needed. Also apply to other surgestions – Castellano

Answer 4

Since the advent of datetime2 datatype, I have been struggling with this problem. To calculate the end of day as a datetime2 datatype I add the number of seconds in a day to the =date= then subtract 100 nanoseconds. Voila:

declare @bod datetime2

declare @eod datetime2 

set @bod = cast (GETDATE() as DATE) 

set @eod = DATEADD(ns, -100, DATEADD(s, 86400, @bod))

print @bod

print @eod

-- answer:

2013-12-01 00:00:00.0000000

2013-12-01 23:59:59.9999999

Now I'm off to datetimeoffset data type.

Sash

Sorry, but this assumes he's using a datetime2 type, which I believe aren't available for SQL Server 2005 (although it's probably that he's upgraded since initially posting this question). Beyond that, there's a number of weird things that happen with casting date/time/timestamp types on SQL Server. This also illustrates a commonly held misconception about ranges, which is why I advocate avoiding BETWEEN altogether. – Missie

The datepart nanosecond is not supported by date function dateadd for data type datetime. – Torpedo

Answer 5

You could also do this:

select @endDate = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@endDate), 0)))

when @endDate is '5/3/2013'

Diggings

This one made it much easier to edit my current query. Only needed to edit the one line where I made my variable, instead of going back through and getting rid of '=' everywhere. – Helmet

Answer 6

--Execution / post date is 1 Feb. 2020

-- sql server this month start and end
select DATEADD(month, DATEDIFF(month, 0, getdate()), 0) -- 2020-02-01 00:00:00.000
select DATEADD(second,-1, datediff(day,0,EOMONTH(getdate()))+1) -- 2020-02-29 23:59:59.000

-- sql server this day start and end
select DATEADD(day, DATEDIFF(day, 0, getdate()), 0) -- 2020-02-01 00:00:00.000
select DATEADD(second,-1, datediff(dd,0,getdate())+1) -- 2020-02-01 23:59:59.000

-- sql server last 30 days start and end
select DATEADD(day, -30, DATEDIFF(day, 0, getdate())) -- 2020-01-02 00:00:00.000
select DATEADD(second,-1, datediff(dd,0,getdate())+1) -- 2020-02-01 23:59:59.000

Isla

Answer 7

You can use between if your end date is set to 00:00:00 of the next day:

ColumnName between @StartDate and convert(datetime, convert(date, @EndDate + 1))

This converts the next day to a date, which removes the hours information, then you convert it back to a datetime which adds default hour information: 00:00:00.

Dyke

Answer 8

You can use any variant of the date from parts functions. For example, let's use DATETIMEFROMPARTS:

DECLARE @d DATETIME2(0) = '2011-10-07 04:32:12.000';

SELECT DATETIMEFROMPARTS(YEAR(@d), MONTH(@d), DAY(@d), 23, 59, 59, 0);

-- 2011-10-07 23:59:59

Unpredictable

Answer 9

you could simply strip the original column of time indication. Eg. if it's delivery notes or invoices from a date.. that has a datetime stamp, you could cast/convert it to only date. That way you lookup of >= @datefrom and <= @dateto will work. like where cast('Datetimefield' as date) >= @datefrom and cast('Datetimefield' as date) <= @dateto

Philae

Technically this will work, but performance over large sets will suffer due to the conversion. Adding +1 day and using less than on the argument is still a better solution. This works better if you cha get the datatype in the schema or add another column so you can index on the date only. – Kirkuk

Answer 10

CAST(Start_Date AS DATE) >= '01-01-2024 00:00:00 000000'
CAST(End_Date AS DATE) <= '31-01-2024 00:00:00 000000'

Use the CAST function, it will work.

Filet

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. Can you kindly edit your answer to offer an explanation? – Rattoon

Answer 11

I first convert the original datetime to begin of the day, then add hours and seconds to it:

DECLARE @start DATETIME, @end DATETIME

SET @start = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

SET @end = DATEADD(HOUR, 23, DATEADD(n, 59, @start))

PRINT @start

PRINT @end

Oct 27 2017 12:00AM

Oct 27 2017 11:59PM

Faceoff