passing date variable to sp

[mirza i]

in asp i have this line:
.....
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate, adParamInput,
50, invdate)
cmdUpdatex.Parameters.Append paramsx
...
(the invdate value is a dd/mm/yyyy date)


AND in sql server 2000 sp i have:

....
@invdate nvarchar(100)= null,
....
insert into inv
(
invdate,
)
values
(
@invdate,
)

when i run this it does not work...
so i run profiler and extracted the problem line.
in query analyser it gives me the:
Syntax error converting character string to smalldatetime data type.


what do you guys think is the problem?

 

[Bob Barrows [MVP]]

in asp i have this line:
....
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate,
adParamInput, 50, invdate)
cmdUpdatex.Parameters.Append paramsx
..
(the invdate value is a dd/mm/yyyy date)


AND in sql server 2000 sp i have:

...
@invdate nvarchar(100)= null,
...
insert into inv
(
invdate,
)
values
(
@invdate,
)

when i run this it does not work...
so i run profiler and extracted the problem line.
in query analyser it gives me the:
Syntax error converting character string to smalldatetime data type.


what do you guys think is the problem?

Date format.
http://www.aspfaq.com/show.asp?id=2040

Bob Barrows

 

[¶Ë°­¾ÆÁö]

In my case

Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate, adParamInput,
50, invdate)

=>
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", advarchar,
adParamInput,
20, invdate)

and in sql sp you have to change
@invdate varchar(20) to convert(smalldatetime, @invdate)

 

[mirza i]

i have tried everything (lost 5hrs and gained 1000 gray hairs)
tomorrow i'll try your suggestion.

thanks

 

[CJM]

i have tried everything (lost 5hrs and gained 1000 gray hairs)
tomorrow i'll try your suggestion.

thanks

I think you'll find the link Bob posted has the answers...

In simple, use the ISO-style Date format: e.g. 20041210 (ISO date format
is actually 2004-12-10)

Chris

 

[mirza i]

ok, figured it out.

i have tried everything from changing dateformat in datab to passing dates
instead of varchar and to my great happiness nothing worked.


in datab:
invdate is datetime,8

from asp:
Set paramsx = cmdUpdatex.CreateParameter ("@invdate", adVarChar,
adParamInput, 50, invdate) cmdUpdatex.Parameters.Append paramsx

and in sql server:
(in @ declares)
@invdate nvarchar(100)= NULL,
and
(in prog)
SELECT @invdate = CONVERT(datetime, @invdate, 103)
....

by the way, this was strange one since i don't think i have changed anything
in prog, sproc or in database and yet the app did not work anymore...
so,
lessons learned: as always figuring out what the problem is in a sproc is a
******* nightmare...
in future i will always use yyyymmdd, this app in now too big for that kind
of change.

many thanks for all that have replied

regards

mirza

 

[Chris Hohmann]

I think you'll find the link Bob posted has the answers...

In simple, use the ISO-style Date format: e.g. 20041210 (ISO date format
is actually 2004-12-10)

Actually both are ISO-style formats. CCYYMMDD is the basic format and
CCYY-MM-DD is the extended format. Here's a related thread from Google
Groups:
http://groups-beta.google.com/group..._frm/thread/90fc7bc047a100d1/07d03358416e3859