First, I do realize that I'm responding to a 2 year old post but it may help others that run across it.
I could certainly be incorrect in my perception of what the true underlying problem is that the OP has but I can think of one and only one reason why someone would want to add '23:59:59.9999999' to a "whole" (Date with no time or a midnight time) and that is to use BETWEEN to delineate temporal criteria such as in the following code example...
WHERE SomeDateTimeColumn BETWEEN @StartDate AND @EndDate
The problem there, of course, is if the SomeDateTimeColumn DOES have a time element to it and @EndDate is a "whole" date, then the rows returned will not include any rows where SomeDateTimeColumn contains the desired @EndDate if it has other then a midnight time. In plain English, the criteria will not include most of the end date and so people take to adding the last possible instant of a day as a time (such as '23:59:59.9999999') to the given "whole" date so that all of the rows for the date described by @EndDate are actually returned.
I rarely use the word "NEVER" but I'll go out on a limb and state that even if "whole" dates are "guaranteed" to be the case in a given column, you should NEVER use BETWEEN for temporal criteria because of two things.
- There are no savings in CPU time because BETWEEN resolves to a
=/<=condition behind the scenes. This is known as a Closed/Closed temporal range definition because both end points are INCLUSIVE.
- Because of the Closed/Closed temporal nature of #1 above, you have to go through the gyrations of adding
the most resolute time value you can to the "whole" end date to
cover all contingencies to make your code work for any resolution and to
make it bullet-proof in the face of a possible data-type change of
the column in the future (including datatype changes made by MS).
Because I do strongly suspect that the real reason why the OP wants to add '23:59:59.9999999' to a "whole" date is to include all of a date using BETWEEN, let's simplify using a Closed/Open (Inclusive, First Exclusive) temporal criteria like the following for code that will easily survive any current date/time data-type, which also adds consistency to the method of defining temporal lookup ranges.
WHERE SomeDateTimeColumn >= @StartDate
AND SomeDateTimeColumn < DATEADD(dd,1,@EndDate)
Another word that I rarely use is ALWAYS but the Closed/Open method above will ALWAYS work for any currently available date/time datatype for "whole" day lookups given that @StartDate and @EndDate have been initially assigned as a "whole" date.