473,397 Members | 2,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,397 software developers and data experts.

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 6719
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Kevin Lyons | last post by:
Hello, I am trying to get all of my form elements passed correctly from the following URL: http://www.dslextreme.com/users/kevinlyons/selectBoxes.html to the following URL:...
6
by: Scott Zabolotzky | last post by:
I'm trying to pass a custom object back and forth between forms. This custom object is pulled into the app using an external reference to an assembly DLL that was given to me by a co-worker. A...
2
by: Keith | last post by:
Good Afternoon, New to .Net. I am trying to pass date/time values to a MS Access query depending on what value is selected from a dropdown list box (January, February, etc). I have declared...
3
by: IntraRELY | last post by:
I have the following function, Notice how I am passing the dateInterval as a string. What is the correct way to pass "DateInterval.Year" as a variable to a function? TIA, Steve Wofford...
9
by: laurenq uantrell | last post by:
I've gotten sort of fed up with dealing with regional date settings on the client side and am considering the following scheme - just wondering if anyone has a negative view of it or not: ...
1
by: darrel | last post by:
A common problem I encounter is having to enter either a date OR a null value into a date field in a DB. The problem I hace is that DBNull.value isn't a proper value for a date variable, so I...
2
by: Geoff Cox | last post by:
Hello, The code below is aimed at passing the date in the yyyyMMdd format from the javascript calendar in an html file to the php in a another file which then searches a MySQL database. For...
2
by: milo1955 | last post by:
I have a form that passes a single variable via the GET function to a mysql databse query with the results paginated. The first page of the results work fine, but the link to the second page yields...
5
by: gubbachchi | last post by:
Hi all, How to pass the php date variable to javascript function. Here is the code I have tried out, but this is not recovering the date correctly in the javascript function <html> <script...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.