0

I realise this is quite a common problem banded around forums, but I am at a loss as to what I've done wrong here.

I have a 'Current' table something like:

ID(uniqueidentifier)
Name(nvarchar max)
FileName(nvarchar max)
FileVersion(smallint)
CurrentVersionDate(datetime)

The CurrentVersionDate is entered using the GETDATE() function within SQLserver.

When a user hit's 'Create file' on my website, the details of this table are transferred to a 'History' Table. So I use a stored procedure to select * details from 'Current' and save each value as a variable (i'm using vb.net in a visual studio project). I'll show the date variable as this is the problem. I'm saving this as a 'date' variable:

Dim dDate as Date
dDate = dr("CurrentVersionDate")

Then I send these variables to another stored procedure to enter into the 'History' table:

Execute sp_InsertHistory '" & ID & "','" & strName& "','" & strFile & "','" & intVersion & "','" & dDate & "'"

I declare the date variable in the stored procedure as:

@Date datetime,

with the insert statement:

INSERT INTO History
(ID, Name, FileName, FileVersion, VersionDate)
VALUES
(@ID, @Name, @FileName, @FileVersion, @Date)

The 'History' table has the same setup as the 'Current' table. Stepping through the code, this error is caught:

Error converting data type varchar to datetime

Surely the variables isn't varchar? And even if it is, the string is surely in the correct format for SQLserver because I'm just selecting the date value SQL has given! Any thoughts?

rxs179
  • 3
  • 1
  • 2
  • Don't concatenate the variables into a string, set them as command parameter values. See http://stackoverflow.com/questions/13014044/sending-parameters-to-stored-procedures-vb-net – flup May 13 '13 at 09:01

2 Answers2

0

the string is surely in the correct format for SQLserver

How do you know that? To be safe, I'd use string in unambiguous format: YYYY-MM-DDTHH:MM:SS, e.g. 2013-05-13T01:01:00.

Alexey A.
  • 892
  • 11
  • 15
  • I initially thought that because the date was initially entered within SQL using the SQL getdate() command. But obviously when I retrieve that date in vb.net, it is retrieved in a slightly different format - as @SysDragon pointed out. Should have realised that really, but when I've been staring blankly at the screen for a while all common sense disappears... – rxs179 May 13 '13 at 09:11
0

Since you are putting the date between quotes, is a varchar '" & dDate & "'", but I think the problem you have with the conversion could be that you are retrieving the date in your local format and you have to insert the date in the SQL Server in his own format.

Try inserting the date as a string in this format: dDate.ToString("MM/dd/yyyy HH:mm:ss")

SysDragon
  • 9,692
  • 15
  • 60
  • 89
  • yep well I feel stupid now! the dDate was retrieved as datetime in the format #5/13/2013 10:06:17 AM# - obviously SQL doesn't like the 'AM' bit. thanks for the sanity check, your suggestion worked! – rxs179 May 13 '13 at 09:08