Insert datetime stamp to sql server thru VB application error

This is making me mad,
I have got small vb application where I want to add dattimestamp at various time into sql server table (field is datetime)
but whenever i try to insert it's giving me an error conversion error while converting char format to date fiel etc.
this is what I am trying in VB

Dim EndDate As String
....
DeleteDate = Format(Now(), "yyyy-mm-dd hh:mm:ss")
....
insert into tbl ..Deletedate
am I making any mistake here
the thing is it works sometime
i checked local date setting it's UK english
I tried declaring the date varible as date datatype
my advacne thanks for your help


ramkmrAsked:
Who is Participating?
 
BillAn1Connect With a Mentor Commented:
No, if the data type is date / datetime, ther is no such thing as format. SQLServer is not expecing a format for a datetime.
Only if you convert it to a string will SQLServer expect the string in a specific format. If you are consistant and never use a string, then you will not have a problem. But of you do something like
update tbl set deletedate='08-Aug-2004 09:02:25' where id =10
i.e. you use a string value, then you need to be concerned about formats. If you cannot stick to datetimes everywhere, the next most robust thing is to use the CONVERT function in SQLServer to convert a string back to a datetime
0
 
BillAn1Commented:
can you give exact syntax of insert statement, and what the table actually looks like? and wht the error is youa re getting?
You should be best off, if the table has a datetime datatype, of using a datetime variable, rather than converting to a string. However, a string can work too.
0
 
solution46Commented:
Check on SQL Profiler to see exactly what is being passed through to your server (Profiler is usually in the same place as Query Analyser in your start menu and is pretty intuitive).

I agree with BillAn1, above: pass it through as a datetime datatype and let ADO deal with it.

s46.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ramkmrAuthor Commented:
the code
update tbl set deletedate='08--Aug-2004 09:02:25' where id =10

the table is
id identity
deletedate datetime
...

the error message I have got is
-2147217913 [Microsoft][ODBC SQL Server Driver][Sql Server] The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

in some machine it's working if i declare the variable as date data type in VB
but in some machine it's not
the reason  i found is in vb I am getting date as dd-mmm-yyyy hh:mm:ss where as when it goes to sql server it's looking it should be  mm-dd-yyyy ... this is the root cause for error
my question is how can get rid of this and it should work where ever i implement this

so what dim declaration should i use in VB date or string?
how can i assign current timestamp to that variable in VB?
then how can i keep whatever the format i am entering is should go to sql server as dd-mmm-yyyy hh:mm:ss fianlly regardless of the sql server machine
thanks
0
 
BillAn1Commented:
If you treat the variable as a datetime, then there is no 'format', it is treated as a number, not a string and so VB should be able to talk to SQLServer easily.
In this particular case, the problem is you have an extra - in your string
update tbl set deletedate='08--Aug-2004 09:02:25' where id =10
should be
update tbl set deletedate='08-Aug-2004 09:02:25' where id =10
however, in VB if you use a date datatype, there will be no problem.

In VB, You can use the function now, or you can pass it on to SQLServer, and use the function getdate() - your SQL can be

"update tbl set deletedate = getdate()"
this will assign the current time to deletedate, and you don't have to worry about any variables at all
0
 
ramkmrAuthor Commented:
sorry -- is typing mistake
regarding using the getdate i have got about 7 datetime fields
so sometime i need to add calculation like adding some tiems etc
so i can't use getdate rest of time(i need to get now() in vb at different place like when procedure called datetime,user typed something datetime,after some processing etc)
i did tried using date also directly but found when it's going to sql server it's expecting in mm-dd--yyyy format that's the one causing this error
I can try removing the format as your suggestion
but is there only way we need to enter in sql server as it's default format?
OR can we forcibly enter datetime as per our convenient.
any ideas
0
 
solution46Commented:
One thing I used to do was force the month into an alpha format, (e.g. mmm to give Jan, Feb, etc.). This kinda worked and got around the US / UK date format issue but fell over spectacularly when we tried to run it on a sister company's macines in France.

SQL Server apparently has no idea that Fev, Avr, etc. are French for Feb and Apr and didn't know what to do with them...

Still gotta say, go with BillAn1 and use the datetime format to pass dates through to SQL Server. Declare the parameter as datetime in the sproc and pass it through as a datetime parameter.

Just thought - you are using ADO parameters and a command type of 'stored procedure' to pass things through, aren't you? If you are using a command type of 'text' and building the query string manually, you are in for all sorts of trouble. Please advise if this is the case, and I'll go through using parameters.

Regards,

s46.
0
 
ramkmrAuthor Commented:
thanks guys for your suggestions
it worked now
I changed the variable declaration as string in vb and used format
as well as passed the correct string yyyy-mm-dd ...
now it's perfect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.