"SELECT DISTINCT and ORDER BY"-problem

Hi!

I can't seem to get my query to work. It returns (translated from Swedish):

Microsoft JET Database Engine (0x80004005)
The ORDER BY-instruction (myDate)..."doesn't work with"...DISTINCT.

This is my query (somewhat simplified):

set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT DISTINCT DATEPART(year, myDate) AS 'myColumn_alias' FROM myDB"
sql=sql & " WHERE myName='" & myName & "'"
sql=sql & " ORDER BY myColumn_alias"
rs.Open sql,conn

Is it because I have a WHERE-clause? If so, how should I design my query (I need only the years to populate a "select from" dropdownbox)? Or is it something else?

ThanksI don't think you can refer to a column alias the samw way you refer to a column.

Try:

ORDER BY DATEPART(year, myDate)|||I've tried that, but then I get the message that too few parameters are given and that 1 is expected.

:(|||All I had to do was to use YEAR(myDate) instead of DATEPART(year, myDate). Now it works!

"REPLACE" used in query

I'm not sure how to use REPLACE here. The Query following REPLACE returns a string whose format is A B C D and I'm trying to convert it to 'A','B','C','D' I'm doing something wrong because query analyzer doesn't like something about the way I've written this.

WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @.UserID) AND (Portfolio_Name = @.Portfolio_Name),'''',''')

Entire SPROC
------------------------------
CREATE PROCEDURE _premium_BSH (@.Portfolio_Name NVarChar (50), @.UserID int, @.Symbol VarChar (1500)) AS

SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin],
a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @.UserID) AND (Portfolio_Name = @.Portfolio_Name),'''',''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = '2003 Q3')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal
GOI see what you're trying to do here - and it will only work with dynamic SQL.

You want to construct a query in the form of:

select ... from ... where Symbol in ('A', 'B', 'C', 'D', 'E')

from a string of 'A B C D E'

You'll need to do this:

declare @.Query varchar(8000)
declare @.Search varchar(100)
set @.Search = 'A B C D E'
set @.Query = 'select ... from ... where Symbol in (''' + replace(@.Search, ' ', ''', ''') + ''')'
exec(@.Query)

Have fun. Just one more thing though, if you can post me your table definitions, I can help you normalise them. They're not normalised from what I can see in your query above.|||post me your table definitions

how do I do that?|||Is this what you mean?

CREATE PROCEDURE _premium_BSH (@.Portfolio_Name NVarChar (50), @.UserID int, @.Symbol VarChar (1500)) AS

declare @.Query varchar(8000)

declare @.Search varchar(1000)

set @.Search = 'SELECT Portfolio_Symbols
FROM a_Users_Portfolios
WHERE (UserID = ''' + @.UserID + ''') AND (Portfolio_Name = ''' + @.Portfolio_Name + ''')'

set @.Query = 'SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin],
a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN (''' + replace(@.Search, ' ', ''', ''') + ''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = ''2003 Q3'')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal'

exec(@.Query)

GO|||No, I mean post me your tables - their names, and columns.

e.g.
TableName (Col1 DataType, Col1 DataType, ...)
...|||imsmart.info

LOL|||Hmm, I'm getting more and more confused as to what your inputs are (e.g. what is an example value of @.Symbol) so I'm going to give up. You get the idea, though? With the dynamic query?|||LOL with what?|||This problem is related to your non-normalised database. Normalise your DB and the whole issue will disappear.

"query contained only ignored words "?

When I try to search a word by an ASP web page, it returns error '80040e14
, The query contained only ignored words'. I know the question maybe asked
for many many times and I do search the Internet but cannot find the answer
right for me.
I use Windows 2003 SP1 and SQL server 2000, all are Chinese Simplified
Edition. According to some articles, I empty the noise.chs in the C:
\windows\system32 and C:\Program Files\Common Files\System\MSSearch\Data
\Config, then re-index the fulltext. But the error still exists. Pls help
me, thanks.
Hi. I've got the answer. I add a space to the noise.chs instead of clear
all the content. thanks!
mizi <haha@.haha.com> wrote in
news:Xns9725D5AB6E7B0hahahahacom@.207.46.248.16:

> When I try to search a word by an ASP web page, it returns error
> '80040e14 , The query contained only ignored words'. I know the
> question maybe asked for many many times and I do search the Internet
> but cannot find the answer right for me.
> I use Windows 2003 SP1 and SQL server 2000, all are Chinese Simplified
> Edition. According to some articles, I empty the noise.chs in the C:
> \windows\system32 and C:\Program Files\Common
> Files\System\MSSearch\Data \Config, then re-index the fulltext. But
> the error still exists. Pls help me, thanks.
>

"Next Page" is extremely slow - like it's requerying

I have a report with a single dataset, 3-levels of table grouping, and
returns 5 pages. When I preview the report in VStudio, it returns the 5
pages and I can navigate the pages real quickly. The problem is, when I run
it online via the RS front-end and click "Next Page," it takes a good 30
seconds to load the subsequent page or pages. The query itself runs in about
45 seconds, but why is the paging not near instantaneous?
Thanks,
jI hope you must have asked some questions yourself like
1. Is this report is slow or all the reports are slow?
2. Is your dev environment and production is different then how fast is your
production machine, is the machine slow (database server)?
3. Is Cache enabled/disabled ?
4. Are you doing the dev and online chking on the same machine ?
Probabily you can go to configuration from RS manager screen and check for
the cache options.
Amarnath
"Jordan" wrote:
> I have a report with a single dataset, 3-levels of table grouping, and
> returns 5 pages. When I preview the report in VStudio, it returns the 5
> pages and I can navigate the pages real quickly. The problem is, when I run
> it online via the RS front-end and click "Next Page," it takes a good 30
> seconds to load the subsequent page or pages. The query itself runs in about
> 45 seconds, but why is the paging not near instantaneous?
> Thanks,
> j
>
>|||Absolutely.
1. This report is slow. All the other reports paginate just fine, even those
that use the same datasources.
2. It's a production machine.
3. It's not caching a temporary copy, but neither is any of the other
reports. I set the caching (every 10 minutes) and it's 99% faster, but I
still don't understand. The report shouldn't refresh itself on every page,
only every time it is run.
4. Yes. Would this really matter?
Thanks,
j
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:DC42BC35-CD2A-42EB-BADA-E7FA77FCCA5F@.microsoft.com...
>I hope you must have asked some questions yourself like
> 1. Is this report is slow or all the reports are slow?
> 2. Is your dev environment and production is different then how fast is
> your
> production machine, is the machine slow (database server)?
> 3. Is Cache enabled/disabled ?
> 4. Are you doing the dev and online chking on the same machine ?
> Probabily you can go to configuration from RS manager screen and check for
> the cache options.
> Amarnath
> "Jordan" wrote:
>> I have a report with a single dataset, 3-levels of table grouping, and
>> returns 5 pages. When I preview the report in VStudio, it returns the 5
>> pages and I can navigate the pages real quickly. The problem is, when I
>> run
>> it online via the RS front-end and click "Next Page," it takes a good 30
>> seconds to load the subsequent page or pages. The query itself runs in
>> about
>> 45 seconds, but why is the paging not near instantaneous?
>> Thanks,
>> j
>>|||Absolutely.
1. This report is slow. All the other reports paginate just fine, even those
that use the same datasources.
2. It's a production machine.
3. It's not caching a temporary copy, but neither is any of the other
reports. I set the caching (every 10 minutes) and it's 99% faster, but I
still don't understand. The report shouldn't refresh itself on every page,
only every time it is run.
4. Yes. Would this really matter?
Thanks,
j

"image/jpeg; charset=utf-8", which is not a valid MIMEType

I am trying to use an image with a "Web" source, the url points to an
aspx page on the same machine as the reporting server, this page
returns a jpeg image (a chart) created by some C# code. On the
"Layout" tab of VS.NET the image is displayed but when I switch to the
"Preview" tab I just get the dreaded Red X.
The following warnings are written to the Output window.
Images with external URL references will not display if the report is
published to a report server without an UnattendedExecutionAccount or
the target image(s) are not enabled for anonymous access.
The value of the MIMEType property for the image ?image1' is
"image/jpeg; charset=utf-8", which is not a valid MIMEType.
I have configured the UnattendedExecuionAccount (rsconfig -e...) and
the page which returns the image allows anonymous access but it
doesn't seem to make any difference. What I don't understand is the
message about the invalid MIMEType, the code which returns the image
sets the ContentType="image/jpeg".
Any help much appreciated.I began to get this error right after I installed "Office 2003 Pro".
Do you use MS Office 2003?
"Mark" wrote:
> I am trying to use an image with a "Web" source, the url points to an
> aspx page on the same machine as the reporting server, this page
> returns a jpeg image (a chart) created by some C# code. On the
> "Layout" tab of VS.NET the image is displayed but when I switch to the
> "Preview" tab I just get the dreaded Red X.
> The following warnings are written to the Output window.
> Images with external URL references will not display if the report is
> published to a report server without an UnattendedExecutionAccount or
> the target image(s) are not enabled for anonymous access.
> The value of the MIMEType property for the image â'image1' is
> "image/jpeg; charset=utf-8", which is not a valid MIMEType.
> I have configured the UnattendedExecuionAccount (rsconfig -e...) and
> the page which returns the image allows anonymous access but it
> doesn't seem to make any difference. What I don't understand is the
> message about the invalid MIMEType, the code which returns the image
> sets the ContentType="image/jpeg".
> Any help much appreciated.
>

"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