Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

704 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