Insert datetime stamp to sql server thru VB application error

Posted on 2004-08-23
Medium Priority
Last Modified: 2012-08-13
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

Question by:ramkmr
  • 3
  • 3
  • 2
LVL 17

Expert Comment

ID: 11868179
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.

Expert Comment

ID: 11868239
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.


Author Comment

ID: 11868381
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 17

Expert Comment

ID: 11868523
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

Author Comment

ID: 11868590
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
LVL 17

Accepted Solution

BillAn1 earned 150 total points
ID: 11868744
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

Expert Comment

ID: 11872725
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.



Author Comment

ID: 11878734
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question