"select" statement in a function

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999
Begin
select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN
End
ELSE
if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
Begin
select MIN(lngPersonID) AS PersonID from Persons
where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName
and strMiddleName=@.MiddleName and strLastName=@.LastName
and strNameSuffix=@.NameSuffix and lngSSN=@.SSN
End
ELSE
if @.LastName is not null
BEGIN
select MIN(lngPersonID) as PersonID from Persons
where strLastName=@.LastName
and lngSSN = @.SSN
END
END
return (@.personID)
END

I m having problem with the "Select" function
the error I m getting is
Select statements included within a function cannot return data to a client (error 444)

Cann I use "select" statement in the function? If not what is the alternative?

I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.

ThanksWhy isn't this a stored procedure?|||Brett

It can be, but I guess you answered many of my questions
I still have this question left in me.

Can a stored procedure return a value??
As far as my knowledge goes it returns with a
"select" statement. Is it true?? Is there any way to check the returned value.
I am very much confused with this concept.

Can I make all functions in a sybase database as stored procedures in SQL??

If it doesnot return then I have to make it a function for sure.

Can you please answer these questions?

Thanks|||USE Northwind
GO

CREATE PROC MySproc99 @.HoursToMargarittaville Decimal(15,4) OUTPUT
AS
BEGIN
DECLARE @.Weekend datetime
SELECT @.Weekend = 'Jan 22 2004 17:00:00'
SELECT @.HoursToMargarittaville = DATEDIFF(mi, GetDate(), @.Weekend)/60.00
Return 0
END
GO

DECLARE @.x decimal(15,4), @.rc int
EXEC @.rc = MySproc99 @.x OUTPUT
SELECT @.x, @.rc
GO

DROP PROC mySproc99
GO|||You can use a select function for assignment, but you cannot return the results of a select statement.

Here is your function rewritten. I also cleaned up some of the unnecessary BEGIN/END pairs and used a COALESCE function rather than mutiple IS NULL checks.

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF COALESCE(@.LastName, @.FirstName, @.MiddleName, @.NamePrefix, @.NameSuffix) is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END

Be aware that I think your logic will fail when comparing NULL values, because NULL values cannot be compared to eachother. For example:
-----------
declare @.NULL1 varchar
declare @.NULL2 varchar
if @.NULL1 = @.NULL2 print 'Success' else print 'Failure'
-----------|||Oops. Looked at your code again and I see that you don't allow nulls for the comparison, so COALESCE is not appropriate. Use this instead:

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END|||Yo, blind dude...

You don't think s/he would be better of with a sproc?

especially in light of a (sounds like massive) sybase to sql server conversion?|||Honestly, (MOO of course) I would use a function here, because I imagine this logic could be called by several different procedures. I've never liked using stored procedures that return scalar OUTPUT. I think the implementation is clunky, so since UDFs were introduced they have been my preferred method.|||MOO indeed...

Want to show me what the udf call would look like?|||see if you can not re-code your function to something like

Create function name (@.param...)
Returns Table

AS

Return Seturn Select ... Your SQL Goes here...

See also BOL, In-Line Functions

The only limitation is that you can have a single select sql phrase inside your function.|||I would call the UDF like this: MOO! MOOOOO! MOO MOO MOO MOO MOO!

Whether it would come when I call, I don't know...

"mean" quesiton

Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George CollinsIsn't mean just a synonym for average? Are you referring to the median?
If so, you could try this post by Celko:
http://groups.google.com/groups?hl=en&lr=&c2coff=1&selm=c0d87ec0.0210170857.72734e7c%40posting.google.com
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>|||AVG = mean. Are you referring to median?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins|||On the sql server 2000 book which is downloadable freely from
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp,
there is no functionality for "MEAN" under "Transact-SQL Reference".
You might have to devise a way to calucate the MEAN yourself.
"george collins" wrote:
> Hi,
> I am trying to find if there is already a function something like "SUM" that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
>|||Thanks I thought I had lost it there for a few minutes... I even went and
found my old Statistics book from 1975. Mean is mean. Thanks for your
response, I have been digging for a few hours and everyone thinks that
playing with an average is what I am trying to do.
Thanks you so much I will research your link.
George Collins
"dance2die" <dance2die@.discussions.microsoft.com> wrote in message
news:E71E7205-66A4-4D9E-BE3F-EDF8DA833E3E@.microsoft.com...
> On the sql server 2000 book which is downloadable freely from
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp,
> there is no functionality for "MEAN" under "Transact-SQL Reference".
> You might have to devise a way to calucate the MEAN yourself.
> "george collins" wrote:
>> Hi,
>> I am trying to find if there is already a function something like "SUM"
>> that
>> can do a "MEAN". Average is too misleading for getting the "MEAN" order
>> amount.
>> Open for suggestions.
>> Thanks in advance.
>> George Collins
>>|||I AM WRONG!!!!
I found it all,
thanks everyone.
George
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
> that can do a "MEAN". Average is too misleading for getting the "MEAN"
> order amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>

"mean" quesiton

Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins
Isn't mean just a synonym for average? Are you referring to the median?
If so, you could try this post by Celko:
http://groups.google.com/groups?hl=e...ing.google.com
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
|||AVG = mean. Are you referring to median?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins
|||On the sql server 2000 book which is downloadable freely from
http://www.microsoft.com/sql/techinf...000/books.asp,
there is no functionality for "MEAN" under "Transact-SQL Reference".
You might have to devise a way to calucate the MEAN yourself.
"george collins" wrote:

> Hi,
> I am trying to find if there is already a function something like "SUM" that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
>
|||Thanks I thought I had lost it there for a few minutes... I even went and
found my old Statistics book from 1975. Mean is mean. Thanks for your
response, I have been digging for a few hours and everyone thinks that
playing with an average is what I am trying to do.
Thanks you so much I will research your link.
George Collins
"dance2die" <dance2die@.discussions.microsoft.com> wrote in message
news:E71E7205-66A4-4D9E-BE3F-EDF8DA833E3E@.microsoft.com...[vbcol=seagreen]
> On the sql server 2000 book which is downloadable freely from
> http://www.microsoft.com/sql/techinf...000/books.asp,
> there is no functionality for "MEAN" under "Transact-SQL Reference".
> You might have to devise a way to calucate the MEAN yourself.
> "george collins" wrote:

"mean" quesiton

Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George CollinsIsn't mean just a synonym for average? Are you referring to the median?
If so, you could try this post by Celko:
http://groups.google.com/groups?hl=...ting.google.com
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>|||AVG = mean. Are you referring to median?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins|||On the sql server 2000 book which is downloadable freely from
http://www.microsoft.com/sql/techin...2000/books.asp,
there is no functionality for "MEAN" under "Transact-SQL Reference".
You might have to devise a way to calucate the MEAN yourself.
"george collins" wrote:

> Hi,
> I am trying to find if there is already a function something like "SUM" th
at
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
>|||Thanks I thought I had lost it there for a few minutes... I even went and
found my old Statistics book from 1975. Mean is mean. Thanks for your
response, I have been digging for a few hours and everyone thinks that
playing with an average is what I am trying to do.
Thanks you so much I will research your link.
George Collins
"dance2die" <dance2die@.discussions.microsoft.com> wrote in message
news:E71E7205-66A4-4D9E-BE3F-EDF8DA833E3E@.microsoft.com...[vbcol=seagreen]
> On the sql server 2000 book which is downloadable freely from
> http://www.microsoft.com/sql/techin...2000/books.asp,
> there is no functionality for "MEAN" under "Transact-SQL Reference".
> You might have to devise a way to calucate the MEAN yourself.
> "george collins" wrote:
>

"limit" in sql server

someone suggested I use:

select top 20 * from t
where pkc not in (select top 10 pkc from t order by pkc) order by pkc

to simulate the limit function in mysql.

i want to replace the 10 in the inner select with a variable. When I do this:

select top 20 * from t
where pkc not in (select top @.counter pkc from t order by pkc) order by pkc

it gives me in an error in sproc. Pls help!!It needs to be dynamic

DECLARE @.SQL varchar(8000), @.Counter int
SELECT @.Counter = 10
SELECT @.SQL = 'select top 20 * from t'
+ ' where pkc not in (select top '
+ CONVERT(varchar(15),@.counter)
+ ' pkc from t order by pkc) order by pkc'
SELECT @.SQL
EXEC(@.SQL)

"Hide Duplicates" within Details Grouping

I'm having problems using the "Hide Duplicates" function within the Details
Grouping of a table in a report (using RS SP2, SQL Server 2000, Visual Studio
2003).
The details grouping has two rows, set out as follows:
| CallFlow Name |
| | Outcome Type | Grammar Type | Meaning | Totals
There are about 10 different "CallFlows", each of which is displayed as
above. There are several possible "Outcome Types"(Success, Fail, Hangup etc),
and I want to prevent duplicates within the scope of the Callflow as shown
below:
| CallFlow Name 1 |
| | Success ...
| | Success ...
| | Success ...
The problem is, when setting "Hide Duplicates" for "Outcome Type", it hides
*ALL* of the duplicated information even in the next Callflow. Is there a
conditional expression or formatting that can be applied to this field so
that duplicates are only hidden for that particular table?
ThanksAll the fields should be in one row of the report layout table.
| CallFlow Name | Outcome Type | Grammar Type | Meaning | Totals
In the table's properties, add a group for the CallFlow field.
In the CallFlow cell's properties, click on the Hide Duplicates dropdown
and select the new group created.
GeoSynch
"Laura-Anne" <Laura-Anne@.discussions.microsoft.com> wrote in message
news:B64F7CCE-F9EB-46C1-9C95-87012913AD82@.microsoft.com...
> I'm having problems using the "Hide Duplicates" function within the Details
> Grouping of a table in a report (using RS SP2, SQL Server 2000, Visual Studio
> 2003).
> The details grouping has two rows, set out as follows:
> | CallFlow Name |
> | | Outcome Type | Grammar Type | Meaning | Totals
> There are about 10 different "CallFlows", each of which is displayed as
> above. There are several possible "Outcome Types"(Success, Fail, Hangup etc),
> and I want to prevent duplicates within the scope of the Callflow as shown
> below:
> | CallFlow Name 1 |
> | | Success ...
> | | Success ...
> | | Success ...
> The problem is, when setting "Hide Duplicates" for "Outcome Type", it hides
> *ALL* of the duplicated information even in the next Callflow. Is there a
> conditional expression or formatting that can be applied to this field so
> that duplicates are only hidden for that particular table?
> Thanks
>|||That worked perfectly - thank you very much!
"GeoSynch" wrote:
> All the fields should be in one row of the report layout table.
> | CallFlow Name | Outcome Type | Grammar Type | Meaning | Totals
> In the table's properties, add a group for the CallFlow field.
> In the CallFlow cell's properties, click on the Hide Duplicates dropdown
> and select the new group created.
>
> GeoSynch
>
> "Laura-Anne" <Laura-Anne@.discussions.microsoft.com> wrote in message
> news:B64F7CCE-F9EB-46C1-9C95-87012913AD82@.microsoft.com...
> > I'm having problems using the "Hide Duplicates" function within the Details
> > Grouping of a table in a report (using RS SP2, SQL Server 2000, Visual Studio
> > 2003).
> >
> > The details grouping has two rows, set out as follows:
> >
> > | CallFlow Name |
> > | | Outcome Type | Grammar Type | Meaning | Totals
> >
> > There are about 10 different "CallFlows", each of which is displayed as
> > above. There are several possible "Outcome Types"(Success, Fail, Hangup etc),
> > and I want to prevent duplicates within the scope of the Callflow as shown
> > below:
> >
> > | CallFlow Name 1 |
> > | | Success ...
> > | | Success ...
> > | | Success ...
> >
> > The problem is, when setting "Hide Duplicates" for "Outcome Type", it hides
> > *ALL* of the duplicated information even in the next Callflow. Is there a
> > conditional expression or formatting that can be applied to this field so
> > that duplicates are only hidden for that particular table?
> >
> > Thanks
> >
>
>

"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

"Decode on rowsource for listbox"

Hi.

I have coded the rowsource for my listbox and I need some sort of "decode" function within my SELECT statement. Can someone help?

For example,

Teacher is a checkbox which contains 0 and 1
I need the listbox to show the word "Teacher" if teacher = 1; else show "Assistant"

mylstbox.rowsource = "SELECT Teacher From mytable"

Statement above populates my listbox with 0 or 1. However, I need the word "Teacher" or "Assistant" to show on the listbox.

Help!

Thanks

SHKI found the dolution by using "case when then else"

"Concatenate Null Yields Null"

Thanks to the good graces of members I have found the COALESECE() FUNCTION,
and it should fix my problem but still, I'm confused.
I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
question and according to what I read in the Documents on Line for Sql Server
2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
Sadly it returns a NULL value. What gives'
--
Ed Warren> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
Current session settings override default settings specified at the database
or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> Thanks to the good graces of members I have found the COALESECE()
> FUNCTION,
> and it should fix my problem but still, I'm confused.
> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
> --
> Ed Warren|||Thanks for the enlightenment. That's what is happening. Now how does one
set the value for an (OLe DB session). From the caller or can this be done
on the server side (by setting options for the user', session). I must
admit I'm new to SQL server 2005 and have overlooked many of the 'gottchas'
Again, thanks.
--
Ed Warren
"Dan Guzman" wrote:
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> Current session settings override default settings specified at the database
> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
> connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> > Thanks to the good graces of members I have found the COALESECE()
> > FUNCTION,
> > and it should fix my problem but still, I'm confused.
> >
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> >
> > --
> > Ed Warren
>|||You'll need to set SET CONCAT_NULL_YIELDS_NULL OFF from the client
application after connecting using ODBC/OLE DB. It can be set on the server
side for the duration of a stored procedure execute by including the SET
statement in the proc. However, as you hinted in your original message, the
proper way to handle NULLs is with COALESCE, et. al.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:B59289CA-EA9A-413A-9290-9D12D2C772D0@.microsoft.com...
> Thanks for the enlightenment. That's what is happening. Now how does one
> set the value for an (OLe DB session). From the caller or can this be
> done
> on the server side (by setting options for the user', session). I must
> admit I'm new to SQL server 2005 and have overlooked many of the
> 'gottchas'
> Again, thanks.
> --
> Ed Warren
>
> "Dan Guzman" wrote:
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> Current session settings override default settings specified at the
>> database
>> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default
>> when
>> connecting via OLE DB or ODBC. This includes SQL Server Management
>> Studio.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
>> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
>> > Thanks to the good graces of members I have found the COALESECE()
>> > FUNCTION,
>> > and it should fix my problem but still, I'm confused.
>> >
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> >
>> > --
>> > Ed Warren

" the object invoked has disconnected from its clients"

I′m configuing a transacional replicationand after concluding it does not function, it is in the status initiating and later it gives the message: " the object invoked has disconnected from its clients"
Can you help me?
I have never heard of this error wrt replication.
The following article indicates it is a windows issue and the resolutions hopefully will help: http://www.kbalertz.com/Feedback_293631.aspx
Another poster upgraded his MDAC version to 2.8 and claims this solved his issue, although upgrading MDAC would not seem to be connected to the above article. Anyway, please let us know if you go down either route and how it works out.
Regards,
Paul Ibison