Solved

Insert datetime stamp to sql server thru VB application error

Posted on 2004-08-23
8
2,126 Views
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


0
Comment
Question by:ramkmr
  • 3
  • 3
  • 2
8 Comments
 
LVL 17

Expert Comment

by:BillAn1
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.
0
 
LVL 9

Expert Comment

by:solution46
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.

s46.
0
 

Author Comment

by:ramkmr
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
thanks
0
 
LVL 17

Expert Comment

by:BillAn1
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ramkmr
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
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 50 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
0
 
LVL 9

Expert Comment

by:solution46
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.

Regards,

s46.
0
 

Author Comment

by:ramkmr
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now