Importing Dates in SQL 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Importing Dates in SQL 2000

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-22 : 08:51:20

I've tried everything to get this to work, for hours today and I just can't figure out what I'm doing wrong.

Below is my stored procedure. Everything works fine if I don't insert data into indexdate. But if I keep indexdate as datetime in, then I get this horrible error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

I do not understand why it's saying that it can't convert datetime to integer. I'm not even mentioning anything in my code about integer for the indexdate field.

The datetime I'm passing to @indexdate is in this format:
13/01/2007 12:13:00 PM
I've also tried
2007/01/13 12:13:00 PM

When I run the stored procedure in query analyser, it works fine. When I run it in my ASP code, it fails with the above error. I've checked my data consistency and I defenitely pass legit dates and times to the @indexdate variable.

After the stored procedure I also posted my ASP.Command code for calling the store in asp.

CREATE PROCEDURE ImportIndexData

(@ID int output, @containerid as int, @indexfield1 as nvarchar(255), @indexfield2 as nvarchar(255), @indexfield3 as nvarchar(255),
@indexfield4 as nvarchar(255), @indexfield5 as nvarchar(255), @dod as datetime, @fileno as nvarchar(20), @userid as int, @indexorigin as int, @indextype as int, @companyid as int, @subcoid as int, @filestatus as int,
@deptid as int, @levelid as int, @indextimeid as int, @activitytypeid as int, @objecttypeid as int, @dodapproval as int,
@WaitForContainerWHS as int, @BlockRSSQLUpdates as int, @indexdate as datetime)

as
SET DATEFORMAT dmy
insert into IndexData
(indexdate, containerid, indexfield1, indexfield2, indexfield3, indexfield4, indexfield5, dod, fileno, userid, indexorigin, indextype, companyid, subcoid, filestatus, deptid, levelid, indextimeid, activitytypeid,
objecttypeid, dodapproval, waitforcontainerwhscan, blockrssqlupdates)
values
(@indexdate, @containerid,
@indexfield1, @indexfield2, @indexfield3, @indexfield4, @indexfield5,
@dod, @fileno,
@userid, @indexorigin, @indextype, @companyid, @subcoid, @filestatus, @deptid, @levelid, @indextimeid, @activitytypeid, @objecttypeid, @dodapproval,
@waitforcontainerwhs, @blockRSSQLUpdates)
set @ID = SCOPE_IDENTITY()

insert into activitylog
(activitytypeid, userid, activitydate, indexid, logchangeappid, retrievalid)
values(42, 7,getdate(), @id, 1, 0)
GO

------------------
Here my ASP Code snippet:
set InsertIndex = Server.CreateObject("ADODB.Command")
InsertIndex.ActiveConnection = MM_connectionstring_STRING
InsertIndex.CommandText = "dbo.ImportIndexData"
InsertIndex.CommandType = 4
InsertIndex.CommandTimeout = 0
InsertIndex.Prepared = true
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@RETURN_VALUE", 3, 4)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@ID", 3, 2)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@containerid", 3, 1,8,rsbox.fields.item("newcontainerid").value)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@indexfield1", 200, 1,255,rsbox.fields.item("indexfield4").value)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@indexfield2", 200, 1,255,rsbox.fields.item("indexfield5").value)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@indexfield3", 200, 1,255,rsbox.fields.item("indexfield6").value)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@indexfield4", 200, 1,255,rsbox.fields.item("indexfield7").value)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@indexfield5", 200, 1,255,rsbox.fields.item("indexfield8").value)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@dod", 135, 1,10,dod)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@fileno", 200, 1,10,fileno)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@userid", 3, 1,8,userid)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@indexdate", 135, 1, 40,now())
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@indexorigin", 3, 1,8,indexoriginid)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@indextype", 3, 1,8,indextype)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@companyid", 3, 1,8,companyid)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@subcoid", 3, 1,8,subcoid)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@filestatus", 3, 1,8,filestatus)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@deptid", 3, 1,8,rsbox.fields.item("indexfield10").value)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@levelid", 3, 1,8,LevelID)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@indextimeid", 3, 1,8,IndexTimeID)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@activitytypeid", 3, 1,8,"42")
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@objecttypeid", 3, 1,8,objecttypeid)
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@dodapproval", 3, 1,8,"0")
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@WaitForContainerWHS", 3, 1,8,"0")
InsertIndex.Parameters.Append InsertIndex.CreateParameter("@BlockRSSQLUpdates", 3, 1,8,"0")
InsertIndex.Execute()

Any help would be much appreciated, I donno what else to do!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 08:59:23

Remove this line and add the at the end instead

InsertIndex.Parameters.Append InsertIndex.CreateParameter("@RETURN_VALUE", 3, 4)

E 12°55'05.25"
N 56°04'39.16"

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-22 : 09:23:00

Hi Peso

I moved it to the bottom but then I get an error that my Stored Procedure contains too many arguments - I've always put it at the top of my commands in ASP and it has always worked.

So I'm still struggling with the date import error though...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-22 : 09:34:38

Do you need SET DATEFORMAT dmy?

Madhivanan

Failing to plan is Planning to fail

sshelper
Posting Yak Master

216 Posts

Posted - 2007-08-22 : 09:47:36

Since you are just passing the current date and time, why not use GETDATE() in the stored procedure instead of using NOW() from the client-side. You can even just set a default value for the IndexDate to use GETDATE().

SQL Server Helper
http://www.sql-server-helper.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 11:02:12

Ok, there is no need to actually CREATE the "@return_value" parameter.
It will always be there and you can access it afterwards. ADO will create it for you when returning from the SP.
Remove the @return_value completely.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources