calling sp with datetime parameter from ole db

Hi folks,
I have a huge problem calling a stored proc with a datetime parameter using
ole db (CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Exec
ute Stored Procedure Using CDynamicParmeterAccesor) as a starting point and
modified the sp "sp_getStudent" so that it gets another parameter @.d datetim
e. But now I'm not able to execute this proc any more: rs.open() returns 0x8
0040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?
Kind regards
Thomas
P.S. Any other kind of third parameter (int, float, ...) works well but date
time (smalldatetime) doesn'tHi Thomas
Your errror seems to be:
DB_E_DATAFIELD_OVERFLOW 80040E21 OLE DB Specific:
The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.
Check the values of the parameters you are passing and (possibly) if you are
mixing the values/parameters up (it is better to add the parameters in the
order in which they are declared!)
John
"Thomas Hein" wrote:

> Hi folks,
> I have a huge problem calling a stored proc with a datetime parameter using ole db
(CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Execute Stored Pro
cedure Using CDynamicParmeterAccesor) as a starting point and modified the sp "sp_ge
tSt
udent" so that it gets another parameter @.d datetime. But now I'm not able to execute this
proc any more: rs.open() returns 0x80040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?rkred">
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well but da
tetime (smalldatetime) doesn't
>|||The error number refers to an overflow error - since the target column
is smalldatetime, I'd check the date value being put in and make sure
it's a valid smalldatetime (which is quite different than datetime).
valid smalldatetime values are Jan 1, 1900 to June 6, 2079.
Thomas Hein wrote:
> Hi folks,
> I have a huge problem calling a stored proc with a datetime parameter
> using ole db (CDynamicParameterAccessor). I used the sample from MSDN
> (HOWTO: Execute Stored Procedure Using CDynamicParmeterAccesor) as a
> starting point and modified the sp "sp_getStudent" so that it gets
> another parameter @.d datetime. But now I'm not able to execute this proc
> any more: rs.open() returns 0x80040e21 (DB_E_ERRORSOCCURRED). What's
> wrong?!?
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well but
> datetime (smalldatetime) doesn't
>|||Hi John, hi Trey,
thank's for the info - the problem is that I do not make any parameter-bindi
ng on my own, I just call
rs.Create(session, "exec sp_getstudent ?, ? out, ? out");
rs.Prepare();
rs.BindParameters(&rs.m_hParameterAccessor, rs.m_spCommand, &pDummy);
the same way as I do without the datetime parameter, but the next call to
rs.Open(NULL, NULL, false);
returns the error stated below...
Kind regards
Thomas
"Thomas Hein" <thomas.hein@.%nospam%inform-ac.com> schrieb im Newsbeitrag new
s:uledgRAAGHA.2092@.TK2MSFTNGP10.phx.gbl...
Hi folks,
I have a huge problem calling a stored proc with a datetime parameter using
ole db (CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Exec
ute Stored Procedure Using CDynamicParmeterAccesor) as a starting point and
modified the sp "sp_getStudent" so that it gets another parameter @.d datetim
e. But now I'm not able to execute this proc any more: rs.open() returns 0x8
0040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?
Kind regards
Thomas
P.S. Any other kind of third parameter (int, float, ...) works well but date
time (smalldatetime) doesn't|||Hi
I assume that you are setting the first parameter as in the example code?
Does SQL profiler show anything?
John
"Thomas Hein" wrote:

> Hi John, hi Trey,
> thank's for the info - the problem is that I do not make any parameter-bin
ding on my own, I just call
> rs.Create(session, "exec sp_getstudent ?, ? out, ? out");
> rs.Prepare();
> rs.BindParameters(&rs.m_hParameterAccessor, rs.m_spCommand, &pDummy);
> the same way as I do without the datetime parameter, but the next call to
> rs.Open(NULL, NULL, false);
> returns the error stated below...
> Kind regards
> Thomas
> "Thomas Hein" <thomas.hein@.%nospam%inform-ac.com> schrieb im Newsbeitrag
news:uledgRAAGHA.2092@.TK2MSFTNGP10.phx.gbl...
> Hi folks,
> I have a huge problem calling a stored proc with a datetime parameter using ole
db (CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Execute Stored P
rocedure Using CDynamicParmeterAccesor) as a starting point and modified the sp "sp_
get
Student" so that it gets another parameter @.d datetime. But now I'm not able to execute thi
s proc any more: rs.open() returns 0x80040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?darkred">
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well but
datetime (smalldatetime) doesn't
>|||I concur with John - you'll need to run Profiler to see what's acutally
being passed to sql server.
Since you're getting the parameter values from the UI, I even more
confident that you're getting an invalid smalldatetime value.
Thomas Hein wrote:
> Hi John, hi Trey,
> thank's for the info - the problem is that I do not make any
> parameter-binding on my own, I just call
> rs.Create(session, "exec sp_getstudent ?, ? out, ? out");
> rs.Prepare();
> rs.BindParameters(&rs.m_hParameterAccessor, rs.m_spCommand, &pDummy);
> the same way as I do without the datetime parameter, but the next call to
> rs.Open(NULL, NULL, false);
> returns the error stated below...
> Kind regards
> Thomas
>
> "Thomas Hein" <thomas.hein@.%nospam%inform-ac.com
> <mailto:thomas.hein@.%nospam%inform-ac.com>> schrieb im Newsbeitrag
> news:uledgRAAGHA.2092@.TK2MSFTNGP10.phx.gbl...
> Hi folks,
> I have a huge problem calling a stored proc with a datetime
> parameter using ole db (CDynamicParameterAccessor). I used the
> sample from MSDN (HOWTO: Execute Stored Procedure Using
> CDynamicParmeterAccesor) as a starting point and modified the sp
> "sp_getStudent" so that it gets another parameter @.d datetime. But
> now I'm not able to execute this proc any more: rs.open() returns
> 0x80040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well
> but datetime (smalldatetime) doesn't
>

Call to Gurus -- SQLDateTimeAPI

Hey gurus ...

I am in the process of writing a DateTime API for SQL Server 2000 ...
Here's what I have come up with so far ...


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_FirstDayOfMonth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_FirstDayOfMonth]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_LastDayOfMonth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_LastDayOfMonth]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_LastWorkingDayOfMonth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_LastWorkingDayOfMonth]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_WorkingDaysBetweenDates]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_WorkingDaysBetweenDates]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_PopulateTimeSeed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_PopulateTimeSeed]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TimeSeed]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TimeSeed]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Function
fn_FirstDayOfMonth (@.SearchDate datetime)
Returns
Datetime
as
Begin
Declare @.ReturnDate datetime

Select
@.ReturnDate = CalendarDate
from
TimeSeed
where
CalendarYear = Year(@.SearchDate)
and CalendarMonth = Month(@.SearchDate)
and DateOfMonth = 1

Return @.ReturnDate
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create Function
fn_LastDayOfMonth (@.SearchDate datetime)
Returns
Datetime
as
Begin
Declare @.ReturnDate datetime

Select
@.ReturnDate = Max(CalendarDate)
from
TimeSeed
where
CalendarYear = Year(@.SearchDate)
and CalendarMonth = Month(@.SearchDate)
Return @.ReturnDate
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create Function
fn_LastWorkingDayOfMonth (@.SearchDate datetime)
Returns
Datetime
as
Begin
Declare @.ReturnDate datetime

Select
@.ReturnDate = Max(CalendarDate)
from
TimeSeed
where
CalendarYear = Year(@.SearchDate)
and CalendarMonth = Month(@.SearchDate)
and WorkingDayFlag = 1
Return @.ReturnDate
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Function
fn_WorkingDaysBetweenDates (@.SearchDate1 datetime, @.SearchDate2 datetime)
Returns
int
as
Begin
Declare @.CountDate int

Select
@.CountDate = Count(CalendarDate)
from
TimeSeed
where
CalendarDate >= @.SearchDate1
and CalendarDate <= @.SearchDate2
and WorkingDayFlag = 1
Return @.CountDate
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[TimeSeed] (
[CalendarDate] [datetime] NOT NULL ,
[DateOfMonth] [int] NOT NULL ,
[DayOfMonth] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CalendarMonth] [int] NOT NULL ,
[CalendarYear] [int] NOT NULL ,
[FiscalYear] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FiscalMonth] [int] NOT NULL ,
[WorkingDayFlag] [bit] NOT NULL ,
[WorkingHours] [int] NOT NULL ,
[WeekOfYear] [int] NOT NULL ,
[WeekOfMonth] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimeSeed] WITH NOCHECK ADD
CONSTRAINT [PK_TimeSeed] PRIMARY KEY CLUSTERED
(
[CalendarDate]
) ON [PRIMARY]
GO
CREATE INDEX [IX_TimeSeed] ON [dbo].[TimeSeed]([DateOfMonth]) ON [PRIMARY]
GO
CREATE INDEX [IX_TimeSeed_1] ON [dbo].[TimeSeed]([CalendarYear]) ON [PRIMARY]
GO
CREATE INDEX [IX_TimeSeed_2] ON [dbo].[TimeSeed]([CalendarMonth]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure usp_PopulateTimeSeed
as
Declare
@.StartDate datetime
Select
@.StartDate = '19000101'
Set DateFirst 1
While @.StartDate <= '21001231'
begin
insert into TimeSeed
(
IntDate
,CalendarDate
,DateOfMonth
,DayOfMonth
,CalendarMonth
,CalendarYear
,FiscalYear
,FiscalMonth
,WorkingDayFlag
,WorkingHours
,WeekOfYear
,WeekOfMonth
)
Select
Cast (@.StartDate as int)
,@.StartDate
,Day(@.StartDate)
,Datepart(DW,@.StartDate)
,Month(@.StartDate)
,Year(@.StartDate)
,Case
When Month(@.StartDate) <= 3
Then Year(@.StartDate) - 1
Else Year(@.StartDate)
End
,Case
When Month(@.StartDate) <= 3
Then Month(@.StartDate) + 9
Else Month(@.StartDate) - 3
End
,Case
When Datepart(DW,@.StartDate) in (1,7)
Then 0
else 1
End
,8
,Datepart(wk,@.StartDate)
,(Day(@.StartDate)%7)+1

Select
@.StartDate = Dateadd(dd,1,@.Startdate)
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Here's what has me stumped

I'd like to calculate the hours difference for a given start and end datetime in SQL server. Certainly function datediff() can do the job but the tricky part is that the hours calculation should be based on 8 hrs / per business day (from 8:00am to 5:00pm), and weekends (Saturdays and Sundays) are excluded as well.

For example:
start time: 11/10/04 4:00pm
end time: 11/11/04 8:00 am
then the hours difference should be only 1 hour. (if there is fraction, then round to nearest hour).


Now ... remember that the table TimeFeed is modifiable and can be changed to suit our needs.

Any ideas ?

Also ... you can all chip in with your own functions ...You forgot holidaze, daylight savings time (which is different all over the world)...what else...

I think you're still going to needs a "schedule" table...|||I am planning to have a holidaze table .... just started on this API about two hours back ... jump in with ideas ...|||Just to bring this back to the first page ... bump !!!

CALL STORED PROCEDURE IN MY QUERY

I have a stored procedure to calculate difference days between 2 days
excluding wend like following
CREATE PROCEDURE Busdays @.from_dt datetime,@.to_dt datetime ,@.wkends int
output
AS
DECLARE
@.days int,
@.dp int ,
@.cnt int
set @.wkends=0
set @.cnt=0
set @.days=datediff(d,@.FROM_DT,@.TO_DT)
while @.cnt <=@.days
begin
set @.dp=datepart(dw,dateadd(day,@.cnt,@.FROM_D
T))
if @.dp<>1 AND @.dp<>7
set @.wkends=@.wkends+1
set @.cnt=@.cnt+1
END
GO
I would like to pass 2 dates field to this stored procedure to get result
in my query like following
DECLARE @.MYDATE INT
SELECT ACCOUNT_NUMBER, [PROCESS DATE], [OPENED DATE],
EXEC BUSDAYS [PROCESS DATE], [OPENED DATE], @.MYDATE OUTPUT FROM MYTABLE
following SQL is working:
DECLARE @.MYDATE INT
EXEC BUSDAYS '3/20/2005', '3/30/2005', @.MYDATE OUTPUT
SELECT @.MYDATE AS 'MYDATE'
Is it possible to do this?
Where I did wrong here?
May I get return value in my query?
Any information is great appreciated,
Souris,souris
DECLARE @.MYDATE INT
EXEC BUSDAYS '3/20/2005', '3/30/2005', @.MYDATE OUTPUT
SELECT ACCOUNT_NUMBER, [PROCESS DATE], [OPENED DATE],@.mayDate FROM Tabel
"souris" <soukkris@.viddotron.com> wrote in message
news:e4nq6MbNFHA.2612@.TK2MSFTNGP10.phx.gbl...
> I have a stored procedure to calculate difference days between 2 days
> excluding wend like following
> CREATE PROCEDURE Busdays @.from_dt datetime,@.to_dt datetime ,@.wkends int
> output
> AS
> DECLARE
> @.days int,
> @.dp int ,
> @.cnt int
> set @.wkends=0
> set @.cnt=0
> set @.days=datediff(d,@.FROM_DT,@.TO_DT)
> while @.cnt <=@.days
> begin
> set @.dp=datepart(dw,dateadd(day,@.cnt,@.FROM_D
T))
> if @.dp<>1 AND @.dp<>7
> set @.wkends=@.wkends+1
> set @.cnt=@.cnt+1
> END
> GO
>
> I would like to pass 2 dates field to this stored procedure to get result
> in my query like following
> DECLARE @.MYDATE INT
> SELECT ACCOUNT_NUMBER, [PROCESS DATE], [OPENED DATE],
> EXEC BUSDAYS [PROCESS DATE], [OPENED DATE], @.MYDATE OUTPUT FROM MYTABLE
> following SQL is working:
> DECLARE @.MYDATE INT
> EXEC BUSDAYS '3/20/2005', '3/30/2005', @.MYDATE OUTPUT
> SELECT @.MYDATE AS 'MYDATE'
>
> Is it possible to do this?
> Where I did wrong here?
> May I get return value in my query?
> Any information is great appreciated,
> Souris,
>
>
>

calendar format

Hi, everybody.
I have a problem with the calendar.
I have a parameter from datetime type.
But I want to change this calendar's format.
When I click the calendar button near the parameter textbox and select a date like 14.3.2001, 3.14.2001 is written to the textbox. And this causes to an error.(Because 14 can't be a month number)
How can I change that parameter's format or calendar's format?
Any idea about this?The date supplied to the calendar is based on the accept language for the browser. In IE look at Tools->Internet Options->Languages and make sure the top language is what you expect.|||

Daniel Reib wrote:

The date supplied to the calendar is based on the accept language for the browser. In IE look at Tools->Internet Options->Languages and make sure the top language is what you expect.

Thanks I did what you said but it didn't work.
Because I am not working on IE now .
I am working on Reporting Services side on .NET.
I changed regional settings from control panel too but nothing different happened.
I couldn't see the calendar's properties.
What can I do else?

Calendar Control in Reporting Services 2000

Hi All

Is it possible to add datetime picker (Calendar Control)

in 2000 reporting services

Cheers

The calendar control came out with Reporting Services 2005. It can't be used in 2000 as far as I know.|||I can second that.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Calendar Control in Reporting Services 2000

Hi All

Is it possible to add datetime picker (Calendar Control)

in 2000 reporting services

Cheers

The calendar control came out with Reporting Services 2005. It can't be used in 2000 as far as I know.|||I can second that.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

calculation with dates challenge

I have a start and end datetime field and a field with the lunchbreak in
minutes. Now I need to find out the number of hours and minutes (HH:mm) this
person has worked.
endtime - starttime - lunchbreak and formatted in HH:mm.
Suggestions on how to go about this in Reporting services ?
PeterPeter,
You will want to do the following:
1) Do a 'DateDiff' and convert the endtime-starttime into minutes
e.g. DateDiff(m, Date1, Date2)
2) Do simple math to subtract the lunch break (if not in minutes, then
convert)
3) Divide the answer by 60 to get your hours
4) Take total minutes minus (60*hours) and this is your remainder minutes
5) format as desired.
if you require more info (ie, how to do date diff, or write it) let me know.
Michael
"Peter De Rop" wrote:
> I have a start and end datetime field and a field with the lunchbreak in
> minutes. Now I need to find out the number of hours and minutes (HH:mm) this
> person has worked.
> endtime - starttime - lunchbreak and formatted in HH:mm.
> Suggestions on how to go about this in Reporting services ?
> Peter

Calculating with datetime fieldtypes

Hello,
I'm calculating with 'datetime' fields and have the following
question/problem.
To get a period I subtract the field <Timefrom> from the field
<Timetill>. Both field have the type 'datetime'.
2005-09-22 15:00:00.000 - 2005-09-22 13:30:00.000
= 1900-01-01 01:30:00.000
This is working OK.
After this I would like to sum the results.
Then I get the message:
[Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a datetime data type
as an argument.]
How can I solve this, keeping the time (hours and minutes)?
NB. In my situation the period is smaller than 1 day.
Hans
*** Sent via Developersdex http://www.examnotes.net ***Take a look a DATEDIFF system function.
"Hans" <nospam@.devdex.com> wrote in message
news:%23Ma0eF2vFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I'm calculating with 'datetime' fields and have the following
> question/problem.
> To get a period I subtract the field <Timefrom> from the field
> <Timetill>. Both field have the type 'datetime'.
> 2005-09-22 15:00:00.000 - 2005-09-22 13:30:00.000
> = 1900-01-01 01:30:00.000
> This is working OK.
> After this I would like to sum the results.
> Then I get the message:
> [Server: Msg 409, Level 16, State 2, Line 1
> The sum or average aggregate operation cannot take a datetime data type
> as an argument.]
> How can I solve this, keeping the time (hours and minutes)?
> NB. In my situation the period is smaller than 1 day.
> Hans
> *** Sent via Developersdex http://www.examnotes.net ***|||SELECT
DATEADD(MI,SUM(DATEDIFF(MI,timefrom,time
till)),0)
FROM YourTable ;
or
SELECT CONVERT(CHAR(5),
DATEADD(MI,SUM(DATEDIFF(MI,timefrom,time
till)),0),14)
FROM YourTable ;
David Portas
SQL Server MVP
--|||Hwo about some custom formatting for that, and the use of datediff:
CREATE FUNCTION Formatminutes
(
@.Minutes INT
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @.Hours INT
SET @.Hours = @.minutes/60
SEt @.Minutes = @.Minutes - @.hours * 60
RETURN (Select CAST(@.hours AS VARCHAR(10)) + ':' + RIGHT('00' +
CAST(@.Minutes AS VARCHAR(10)),2))
END
Select dbo.Formatminutes(ABS(Datediff(mi,'2005-09-22
15:01:00.000','2005-09-22 13:30:00.000')))
HTH, jens Suessmeyer.|||try this
SELECT FLOOR(SUM(DATEDIFF(SS,startdate,enddate)
)/60) as Minitues,
SUM(DATEDIFF(SS,startdate,enddate))%60 as seconds FROM <tablename>
use can straigh away use 'm' in place of ss also in the first one
Regards
R.D
"Uri Dimant" wrote:

> Take a look a DATEDIFF system function.
>
> "Hans" <nospam@.devdex.com> wrote in message
> news:%23Ma0eF2vFHA.2728@.TK2MSFTNGP14.phx.gbl...
>
>