469,275 Members | 1,575 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

passing date variable to sp

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?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004
Jul 22 '05 #1
6 6528
mirza i wrote:
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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #2
˰
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" <us*****@hotmail.com> wrote in message
news:#c*************@TK2MSFTNGP09.phx.gbl...
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?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004

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

thanks
"˰*" <dd******@haja.or.kr.korea> wrote in message
news:ep**************@TK2MSFTNGP14.phx.gbl...
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" <us*****@hotmail.com> wrote in message
news:#c*************@TK2MSFTNGP09.phx.gbl...
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?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004
Jul 22 '05 #4
CJM

"mirza i" <us*****@hotmail.com> wrote in message
news:eb***************@TK2MSFTNGP10.phx.gbl...
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
Jul 22 '05 #5

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

"CJM" <cj*******@newsgroups.nospam> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

"mirza i" <us*****@hotmail.com> wrote in message
news:eb***************@TK2MSFTNGP10.phx.gbl...
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

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.808 / Virus Database: 550 - Release Date: 08/12/2004
Jul 22 '05 #6
"CJM" <cj*******@newsgroups.nospam> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

"mirza i" <us*****@hotmail.com> wrote in message
news:eb***************@TK2MSFTNGP10.phx.gbl...
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)


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/...d03358416e3859
Jul 22 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Scott Zabolotzky | last post: by
2 posts views Thread by Keith | last post: by
3 posts views Thread by IntraRELY | last post: by
2 posts views Thread by Geoff Cox | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.