stored procedure can't execute date format problem 1

[xtremeLogic]

Hi,

I have a sql server stored procedure that I would like to execute from vb.net. The problem is that it won't complete the insert into the table because of the date being sent. I am getting the date from a form field so I format it like

Format("{0:MM/dd/yyyy}", docDate)

However, it does not complete the stored proc due to the date format being too long. I traced the execution of the stored proc and got the following date parameter being sent to the db:

'Jan 20 2004 12:00:00:000AM'

The problem with that is that because of the length of it, the db doesn't accept. The field in my db is of type datetime.

Any suggestions would be greatly appreciated

 

[JayKusch]

May be the time portion ... should be ...
'Jan 20 2004 12:00:00.000AM'


Thanks

J. Kusch

 

[xtremeLogic]

Thanks for the input,

but I do not want the input parameter to be that long as the db doesn't accept it. My guess is that the stored procedure converts the date input (which is a string) into this long date format which my db won't accept.

Still stuck....

 

[JayKusch]

What type of DB are you inserting this date into? I am able to insert/update that date string into a SS2K table field that is set to DateTime w/out a problem ...


Thanks

J. Kusch

 

[xtremeLogic]

I'm using MS SQL Server 2k and the field type is DateTime. Here's some code, hopefully u could spot my problem:

VB.NET STORED PROC PARAMETER:

Dim parameterCreateDate As New SqlParameter("@docDate", SqlDbType.DateTime)
parameterCreateDate.Value = Format(docDate, "MM/dd/yyyy")
myCommand.Parameters.Add(parameterCreateDate)

STORED PROC:

lter PROCEDURE sp_AddDocument
    @ID INT,
    @ProjectID INT,
    @DocDate DATETIME,
    @ToCompany VARCHAR(50),
    @ToPerson VARCHAR(50),
    @FromCompany VARCHAR(50),
    @FromPerson VARCHAR(50),
    @Subject VARCHAR(50),
    @Reference VARCHAR(50),
    @DocType VARCHAR(20),
    @Keywords text,
    @FileName VARCHAR(50),
    @FilePath VARCHAR(255),
    @DocSize VARCHAR(20)
AS
SET NOCOUNT ON
DECLARE @DocRoomID INT
BEGIN TRAN
  IF(@ID = 0)
   BEGIN
   INSERT INTO DocRoom
     (UploadDate, fk_projectID, DocDate, FromCompany, FromPerson, Subject, Reference, DocType, DocSize, Keywords, FileName, FilePath)
   VALUES
     (GETDATE(), @projectID, @DocDate, @FromCompany, @FromPerson, @Subject, @Reference, @DocType, @DocSize, @Keywords,
     @FileName, @FilePath)
   SELECT @DocRoomID = @@IDENTITY 
   INSERT INTO DocRoomDetails
    (fk_docRoomID, ToCompany, ToPerson)
   VALUES
    (@DocRoomID, @ToCompany, @ToPerson)
   END
  ELSE
   BEGIN
    UPDATE DocRoom Set
     DocDate=@DocDate,
     FromCompany=@FromCompany,
     FromPerson=@FromPerson,
     Subject=@Subject,
     Reference=@Reference,
     DocType=@DocType,
     DocSize=@DocSize,
     Keywords=@Keywords
    WHERE pk_DocRoomID = @ID
   END
COMMIT TRAN

 

[JayKusch]

Well I know we are hung on the Milliseconds portion of the time being passed. It is being passed with a : as a prefix000AM') where it should be a . (.000AM')

If we can pass the date in as a string, we can run a couple of Converts/Substrings and such to parse a useable date. Is there a VB function that will return just the date and time minus the milliseconds?


Thanks

J. Kusch

 

[xtremeLogic]

I tried testing the query in ms access just to see what parameters would work. When I enter the following:

Jan 20 2004 12:00:00.000AM

I get an error saying that the value you entered isn't valid for this field.

So I know I have to just get the Date Portion and not the time. The formatting function that I used in vb keeps it in just date format, but I believe because my stored proc parameter is type datetime, it might be converting it into the long format.

Still stuck...

 

[JayKusch]

Try this in query analyzer and lets see how close we get ...
may need to adjust the numbers in the SubString +/- 1.

DECLARE @DateDoc VarChar(20)
DECLARE @DateDocT VarChar(20)

SET @DateDoc = 'Jan 20 2004 12:00:00:000AM'

SET @DateDocT =

SUBSTRING(@DateDoc,1,Len(@DateDoc) - 7) + '.' +
SUBSTRING(@DateDoc,Len(@DateDoc) - 5,Len(@DateDoc))

Print 'Test Date Doc = ' + @DateDocT

Thanks

J. Kusch

 

[JayKusch]

Are we inputing into SQL Server or Access?


Thanks

J. Kusch

 

[JayKusch]

Scratch the above example ... I was able to run this in QA and get the desired result ...


DECLARE @DateDoc VarChar(30)
DECLARE @DateDocT VarChar(30)

SET @DateDoc = 'Jan 20 2004 12:00:00:000AM'

SET @DateDocT =

SUBSTRING(@DateDoc,1,Len(@DateDoc) - 6) + '.' +
SUBSTRING(@DateDoc,Len(@DateDoc) - 4,Len(@DateDoc))

Print 'Test Date Doc = ' + @DateDocT


Thanks

J. Kusch