Solved

Insert datetime stamp to sql server thru VB application error

Posted on 2004-08-23
8
2,125 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ramkmr
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

9 Experts available now in Live!

Get 1:1 Help Now