"end of day" function?

I'm trying to write a function that returns the last millisecond of a day.
That way I can do queries like...
... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
endOfDay(getdate())
Now it would be nicer if there was simply a "date" type, as opposed to
datetime, but whatever.
Anyway, I tried this...
ALTER FUNCTION dbo.EndOfDay
(@.date datetime) RETURNS datetime
BEGIN
RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
END
StartOfDay works fine, I've tested it extensively. However EndOfDay returns
the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
effect, and it only starts to work when the number is => than 1 second. That
might work OK for my needs, but I'd much prefer to understand what's going o
n
here and fix it.
MauryMaury Markowitz,
I think you meant:

> ... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
> endOfDay(getdate())
...
WHERE entrydate >= startOfDay(getdate()) and entrydate <= endOfDay(getdate()
)
...
for this kind of filter, it is better to use this pattern:
...
WHERE entrydate >= convert(char(8), getdate(), 112) and entrydate <
dateadd(day, 1, convert(char(8), getdate(), 112))
...
AMB
"Maury Markowitz" wrote:

> I'm trying to write a function that returns the last millisecond of a day.
> That way I can do queries like...
> ... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
> endOfDay(getdate())
> Now it would be nicer if there was simply a "date" type, as opposed to
> datetime, but whatever.
> Anyway, I tried this...
> ALTER FUNCTION dbo.EndOfDay
> (@.date datetime) RETURNS datetime
> BEGIN
> RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
> END
> StartOfDay works fine, I've tested it extensively. However EndOfDay return
s
> the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
> effect, and it only starts to work when the number is => than 1 second. Th
at
> might work OK for my needs, but I'd much prefer to understand what's going
on
> here and fix it.
> Maury
>|||"Alejandro Mesa" wrote:
> for this kind of filter, it is better to use this pattern:
> ...
> WHERE entrydate >= convert(char(8), getdate(), 112) and entrydate <
> dateadd(day, 1, convert(char(8), getdate(), 112))
> ...
Sure, but why doesn't it work? Why is tommorrow -1 ms still tomorrow?
Maury|||Maury Markowitz,
If you check BOL, you can read that SQL Server stores datatiem data type to
an accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds or 0.00333 seconds). So the last millisecond will be 997, 998
and 999 will be interprete as tomorrow. That is why it recommended to use th
e
other pattern.
AMB
"Maury Markowitz" wrote:

> "Alejandro Mesa" wrote:
> Sure, but why doesn't it work? Why is tommorrow -1 ms still tomorrow?
> Maury|||SQL's DATETIME datatype is only precise to 3ms so the value gets
rounded up to the following day. Use >= and < as Alejandro suggests. It
makes queries simpler and easier to read plus it's perhaps unwise to
rely on DATETIME always being stored to the nearest 3ms - maybe that
might change in the future.
David Portas
SQL Server MVP
--|||"Alejandro Mesa" wrote:
> an accuracy of one three-hundredth of a second (equivalent to 3.33
> milliseconds or 0.00333 seconds). So the last millisecond will be 997, 998
Ahhh. Thanks!
Maury|||Although I agree with the other respondents, I do always loving interesting
questions, and this is one of them.
First of all, the DATETIME data type is stored as two INT data types. The
first is a count of days from the reference date, where 0 day = 1/1/1900.
The second is a count of "ticks" since midnight, where each "tick" is
1/300th of 1 second ~ 3.33 ms.
It is my opinion that whenever precision is required, always use the
primative data types. So, try this:
CREATE FUNCTION dbo.EndofDay
(@.date AS DATETIME)
RETURNS DATETIME
AS
/*
**
** The DATETIME data type is really two
** Integer segments in binary representation.
**
** The first segment counts the number of days
** from a reference date (day 0 = 1/1/1900).
**
** The Second segment counts the number of "ticks"
** since midnight, where a "tick" is defined as 1/300th of 1 second.
**
** This function manipulates the time segment by truncating
** that segment from the original DATETIME parameter and
** replacing it with one of maximum count.
**
*/
BEGIN
DECLARE @.intDatePart AS INT
,@.intTimePart AS INT
,@.vbnDateTime AS VARBINARY(8)
,@.dtmDateTime AS DATETIME
-- Make sure the parameter was passed correctly.
SET @.dtmDateTime =
COALESCE(@.date, 0)
-- Convert the data type to one that can be character manipulated.
SET @.vbnDateTime =
CAST(@.dtmDateTime AS VARBINARY(8))
-- Slice out the first 4 characters as the date part.
SET @.intDatePart =
CAST(CAST(LEFT(@.vbnDateTime, 4) AS VARBINARY(4)) AS INT)
-- Since we want the end of the same day,
-- we set to max "tick" before the next day rollover.
-- 24 hours x 60 minutes x 60 seconds x 300 1/300ths of a second.
SET @.intTimePart = 25919999
-- Now convert our pieces back to the correct data type.
SET @.dtmDateTime =
CAST(CAST(
CAST(CAST(@.intDatePart AS VARBINARY(4)) AS VARCHAR(4)) +
CAST(CAST(@.intTimePart AS VARBINARY(4)) AS VARCHAR(4))
AS VARBINARY(8))
AS DATETIME)
RETURN @.dtmDateTime
END
Good Luck.
Sincerely,
Anthony Thomas
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:8EEE1EB7-03FD-48BC-8CF9-7B3E50B52E28@.microsoft.com...
I'm trying to write a function that returns the last millisecond of a day.
That way I can do queries like...
... WHERE entrydate >= startOfDay(getdate()) and entrydate >=
endOfDay(getdate())
Now it would be nicer if there was simply a "date" type, as opposed to
datetime, but whatever.
Anyway, I tried this...
ALTER FUNCTION dbo.EndOfDay
(@.date datetime) RETURNS datetime
BEGIN
RETURN dateadd(ms, -1, dateadd(d, 1, dbo.StartOfDay(@.date)))
END
StartOfDay works fine, I've tested it extensively. However EndOfDay returns
the start of tomorrow. Any idea why? I tried upping the -1 to -100 with no
effect, and it only starts to work when the number is => than 1 second. That
might work OK for my needs, but I'd much prefer to understand what's going
on
here and fix it.
Maury