"Simple" query help

Hello!
I would like some help to figure out how to write a specific update query.
What I'm trying to do:
I have one table with these columns:
no (primary key or clustered?)
object (primary key or clustered?)
cid
usergroup1
usergroup2
Lets say the table I would like to upgrade is called obj1 and the table I'm
getting the value to set from is called obj2.
This is in real the same table and it's called objectx.
I would like to set cid for the records with no=7 in obj1 to the value of
the records in obj2 with:
obj2.no=1,
obj2.usergroup2=obj1.object
the value should be set to obj2.usergroup1
The obj2.usergroup2 values could be found several times.
Any idea how to write this query?
Regards MagnusOn Fri, 9 Dec 2005 12:16:41 +0100, Magnus Blomberg wrote:

>Hello!
>I would like some help to figure out how to write a specific update query.
>What I'm trying to do:
>I have one table with these columns:
>no (primary key or clustered?)
>object (primary key or clustered?)
>cid
>usergroup1
>usergroup2
>Lets say the table I would like to upgrade is called obj1 and the table I'm
>getting the value to set from is called obj2.
>This is in real the same table and it's called objectx.
>I would like to set cid for the records with no=7 in obj1 to the value of
>the records in obj2 with:
>obj2.no=1,
>obj2.usergroup2=obj1.object
>the value should be set to obj2.usergroup1
>The obj2.usergroup2 values could be found several times.
>Any idea how to write this query?
>Regards Magnus
>
Hi Magnus,
Before writing the query, the specifications should be clear. You say
that obj2.usergroup2 can be found several times. That means that there
might be more than one obj2.usergroup1. Which one of these should be
used to set obj1.cid'
If you need the lowest value, try if this works:
UPDATE objectx
SET cid = (SELECT MIN(obj2.usergroup1)
FROM objectx AS obj2
WHERE obj2.no = 1
AND obj2.usergroup2 = objectx.object)
WHERE no = 7
(untested - if you prefer a tested reply or if this doesn;t work, then
please check www.aspfaq.com/5006 to find out how to provide clear specs
and test data).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

"Macro" statement

Is it possible to write a macro statement using Transact-SQL?
Imagine that we have a table named tblA and fields with the almost
same name, for example Field01, Field02,...Field20. (I have named
fields on that way for the better explanation).
Now, suppose that we want to do almost the same update on all of the
fields:
UPDATE tblA
SET Field01 = 100000
UPDATE tblA
SET Field02 = 100000
and so on...
(we must write 20 identical statements). This example is very simple
(please, forget the solution with one statement because it is clear!).
I wrote a simple example because of my next explanation and question.
In some other languages it is not necessarily to write 20 almost
identical statements. I can write something like this:
FOR i: = 1 TO 20
cTemp := CHAR2(i)
REPLACE Field&cTemp with 10000
NEXT i
-- cTemp (using CHAR2 convert function) have a character values: '01',
'02', '03'... etc.
As you can see, with every step through the loop I have changed the
statements using macro Field&cTemp.
Is it possible to write a similar solution in Transact SQL and avoid
20 identical statements?DECLARE @.i int, @.qry varchar(500)
SET @.i=1
WHILE @.i<=20 BEGIN
SET @.Qry='UPDATE tblA SET Field'+Cast(@.i as varchar)+'=100000'
EXEC(@.Qry)
SET @.i=@.i+1
END
This is not the most efficient method but it closely follows your
example(minus the 0 prefix on the first 9 fields). Better would be to build
up the string for a single update to all columns but I'll leave that to you
:)
Mr Tea
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||A couple of questions:
1. Is there a where clause, or is this a single row table?
2. Updating the same row or rows twenty different times is not a very
efficient approach (it will end up taking twenty different log writes!)
3. How are you matching the field with the value?
In general it is far better when it comes to SQL to execute fewer complex
statements than many simpler statements. Building the proper statement and
executing it will be far better. So you could write something like:
--not meant to be compilable, pseudocode only
set @.query = 'UPDATE tblA --hopefully not your real table name'
set @.query = 'SET '
set @.i = 1
while @.i < 20
begin
set @.query = @.query + 'Field' + cast(@.i as varchar(2)) + ' = 100000, '
set @.i = @.i + 1
end
set @.query = @.query + 'WHERE --and your where clause'
exec (@.query)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||Thanks a lot Mr Tea and Mr Davidson
It's work (on my more complex task). :-)
And of course - answers:
1. I have a another table (not this one for update). That table
contains circular nodes of hieararchy. This is the reason why I must
first fullfill columns step-by-step.
2. ...it means: yes, I have a WHERE clause and FROM clause (JOIN with
circular table) also.
3. Yes, .log file is written 20 times but I will sucrifise that. I
work with basic data (corporate hierarchy) - not huge set of rows.
4. Of course, matching values in my example is not so simple. I
matching a values with another one 'macro' that read a data form
another table.
...and all of that because you help me! :-)
So,
Thank you once again
Mr Zaratino|||>> Imagine that we have a table named tblA and fields [sic] with the
almost same name, for example Field01, Field02,...Field20. (I have
named fields [sic] on that way for the better explanation). <<
A column is not a field -- nothing like it at all. Since each column
is a separate attribute of the entity in your data model, it would be
VERY unusual to have such a table if you had a proper data model.
However, if I were writing a 1950's file system (files are made of
records which do have fields), then they would probably be a repeating
group -- and a violation of First Normal Form (1NF).
fields [sic]: <<
In SQL an UPDATE works on entire rows (rows are not records), changing
all the columns at the same time.
UPDATE Foobar
SET x = <value1>,
y = <value2>,
z = <value3>,
etc.
If you want to pass the values as parameters, then you can skip some of
them by passing a NULL and having this SET clause in your UPDATE
statement.
SET x = COALESCE (<value1>, x)
Dynamic SQL generation is considered very poor design; it says you have
no data model and no idea what to do until run time.|||>> That table contains circular nodes of hieararchy. This is the reason
why I must
first fullfill columns step-by-step. <<
Do you mean that you are using an adjacency list model for a hierarchy?
If so, look up the nested set model instead. Otherwise, you are not
usingthe power of a set-oriented language and have re-invented a file
system.|||Yes, Celko - everything that you said is correct, I understand UPDATE
statement; sorry for my confusion about 'fields' and 'columns'.
My congratulation, you recognize that I violate 1NF but there is a
good reason for that. I need that look of table for further purpose
(cube). With table like this the next actions are faster...(sometimes
this is even necessarly).
Thanks,
Zaratino

"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

"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 on
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 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
>|||"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 the
other pattern.
AMB
"Maury Markowitz" wrote:
> "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|||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

"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 on
here and fix it.
Maury
Maury 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 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
>
|||"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 the
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