- Follow via:
- RSS
- Email Alert
Question
0
Votes
VB 6 and SQL
I have developed an Inventory control system using Visual basic 6 and back end as Sql Server 2000.
When i try to access data through below mentioned SQL string,
rstGrn.Open "Select stgrnsub.grnqty From StGrnSub Inner join StGrnMain on stgrnsub.grnref = stgrnmain.grnref Where ((StGrnSub.grnicode = '" & sICode & "') And ((stgrnmain.grndt >= '" & dtOSdt & "') and (stgrnmain.grndt = '" & dtOEdt & "')))", goConn, adOpenStatic, adLockPessimistic
it gives me an error. That is
Run-time error '2147217913 (80040e07)'. The conversion of a char data type to a datetime data type resulted in an our-of-range datetime value.
So i changed the date format in My PC using control panel regional options from dd-mm-yyyy to yyyy-mm-dd. After this change above sql query worked fine.
My question is that, is there a way to run this sql query without changing the date format of the windows. (Im using win xp)
17th Aug 2009
Answers (5)
0
Votes
If you have fixed it, and it runs, then why change it?
You have just solved it, so why change it?
18th Aug 2009
Replies
Because it affects to other softwares like MYOBE. and other thing is that in most computers default date format is dd-mm-yyyy. So if i am develop a system that system must be perfect (at least 99% perfect).
It is a headache to change this default settings.
Anyway I have found a solution to do what i want. The way I pass date format is not fully correct. To sql server 2000 database we have to arrange the date to YYYYMMDD format and pass it to the table.
Now everything is ok.
Thank you very much your reply
It is a headache to change this default settings.
Anyway I have found a solution to do what i want. The way I pass date format is not fully correct. To sql server 2000 database we have to arrange the date to YYYYMMDD format and pass it to the table.
Now everything is ok.
Thank you very much your reply
dilumd@...
18th Aug 2009
First note that strings passed as date values to sql server are automatically converted to date values using the default date format of the sql server.
The solution you mention relies on your discovery of the default date format of your sql server. This is not guaranteed to be the same in other installations.
Use the following date format...
19 Aug 2009
obtained in VB, at the time of writing, using...
Format(now, "d Mmm YYYY")
The above date format will always be automatically and correctly converted no matter what the default date format of the database server.
The solution you mention relies on your discovery of the default date format of your sql server. This is not guaranteed to be the same in other installations.
Use the following date format...
19 Aug 2009
obtained in VB, at the time of writing, using...
Format(now, "d Mmm YYYY")
The above date format will always be automatically and correctly converted no matter what the default date format of the database server.
bruceanwyl@...
19th Aug 2009
0
Votes
Use Format()
Use the Format function on your date value e.g., format(dtOSdt, "yyyy-mm-dd").
18th Aug 2009
Replies
If you present dates to SQL in ISO format, or yyyymmdd, you should have no problems. Inserting separators will work, but it is not immune to the server's date format expectations, whereas just the numbers are immune to that.
HGunter
18th Aug 2009
0
Votes
Make the date format non ambigious
Because you do not control the computers settings that an application is installed on, I always ensure that the date is not ambigious, so I would do the following
stgrnmain.grndt >= convert(datetime, '" & format(dtOSdt, "dd mmm yyyy") & "')
No matter what settings are on the computer or the SQL server it always interprets dd mmm yyyy correctly.
stgrnmain.grndt >= convert(datetime, '" & format(dtOSdt, "dd mmm yyyy") & "')
No matter what settings are on the computer or the SQL server it always interprets dd mmm yyyy correctly.
18th Aug 2009
Replies
Unambiguous date format is the best way to send to the server for execution, since there is no way it could be mis-interpreted.
You could have a function in VB which "generates" the correct format (dd mmm yyyy) for any date that you pass to it, then you can simply use:
... (stgrnmain.grndt >= convert(datetime, '" & FormatDate(dtOSdt) & "') ...
Or if your grndt field is of datetime data type, without the convert sql function, since the string is an unambiguous datetime and can be correctly implicitly converted to datetime:
... (stgrnmain.grndt >= '" & FormatDate(dtOSdt) & "') ...
You could have a function in VB which "generates" the correct format (dd mmm yyyy) for any date that you pass to it, then you can simply use:
... (stgrnmain.grndt >= convert(datetime, '" & FormatDate(dtOSdt) & "') ...
Or if your grndt field is of datetime data type, without the convert sql function, since the string is an unambiguous datetime and can be correctly implicitly converted to datetime:
... (stgrnmain.grndt >= '" & FormatDate(dtOSdt) & "') ...
arvin_d@...
18th Aug 2009
Using a number only format used to give me problems, but a few years ago I changed all date references to use the "DD-MMM-YYYY" format and have had no problems since then.
HarryJ
18th Aug 2009
0
Votes
Another way of formatting date
You can use VB Format() function as following:
Replace following part of your SQL
And ((stgrnmain.grndt >= '" & dtOSdt & "') and (stgrnmain.grndt = '" & Format(dtOSdt, "yyyy-MM-dd") & "') and (stgrnmain.grndt = '" & Format(dtOEdt, "yyyy-MM-dd") & "')))",
I hope it will also help.
Replace following part of your SQL
And ((stgrnmain.grndt >= '" & dtOSdt & "') and (stgrnmain.grndt = '" & Format(dtOSdt, "yyyy-MM-dd") & "') and (stgrnmain.grndt = '" & Format(dtOEdt, "yyyy-MM-dd") & "')))",
I hope it will also help.
18th Aug 2009
Replies
using a format where the month is formatted as "mm" will run the risk of causing confusion depending on date settings on the Server.
I think it is still better to specify month as mmm, this way, there is no ambiguity.
if you really want to use "mm", then you need to use the "Convert" SQL function to specify the date format.
eg
Convert(Datetime, *date*, 102) for mm/dd/yyyy format
Convert(Datetime, *date*, 103) for dd/mm/yyyy format (aka British date format)
etc.
eg
... stgrnmain.grndt >= Convert(Datetime, '" & Format(dtOSdt, "dd/mm/yyyy") & "', 103) ...
I think it is still better to specify month as mmm, this way, there is no ambiguity.
if you really want to use "mm", then you need to use the "Convert" SQL function to specify the date format.
eg
Convert(Datetime, *date*, 102) for mm/dd/yyyy format
Convert(Datetime, *date*, 103) for dd/mm/yyyy format (aka British date format)
etc.
eg
... stgrnmain.grndt >= Convert(Datetime, '" & Format(dtOSdt, "dd/mm/yyyy") & "', 103) ...
arvin_d@...
19th Aug 2009
0
Votes
error message
Depending on your runtime environment, I think your original error message was caused by incorrect delimiters. For date literals, you need to use date delimiters.
Example
And (stgrnmain.grndt Between #" & dtOSdt & "# And #" & dtOEdt & "#)"
Note: I changed the comparison to Between for (usually) better performance.
Example
And (stgrnmain.grndt Between #" & dtOSdt & "# And #" & dtOEdt & "#)"
Note: I changed the comparison to Between for (usually) better performance.
18th Aug 2009
Replies
I think the Hash (#) symbol used for dates is used within the syntax of VB itself? Say when comparing a date input by the user to another date.
When sending to SQL Server as part of a SQL string, we cannot send # right?
I don't think T-SQL has any # as part of its date formats.
When sending to SQL Server as part of a SQL string, we cannot send # right?
I don't think T-SQL has any # as part of its date formats.
arvin_d@...
20th Aug 2009
@arvin
Since we are making the call from VB6, we are passing the SQL through database layers that do some syntax checking and conversion to a standard ODBC (interop) format. As such, we need to use the proper delimiters to help this conversion process. To complicate matters, we are concatenating the locally formatted date as a string into the SQL statement. I've seen instances where a date string was treated as a numeric expression and the result of the numeric expression was passed into SQL that was passed to the database!
Example:
8/21/2009 might be a date value of a variable included in a SQL statement construction via concatenation. What got passed to the database was 0.00018962
If you were assigning date parameter values, then you wouldn't need to do this, since the parameters are of known data type.
Since we are making the call from VB6, we are passing the SQL through database layers that do some syntax checking and conversion to a standard ODBC (interop) format. As such, we need to use the proper delimiters to help this conversion process. To complicate matters, we are concatenating the locally formatted date as a string into the SQL statement. I've seen instances where a date string was treated as a numeric expression and the result of the numeric expression was passed into SQL that was passed to the database!
Example:
8/21/2009 might be a date value of a variable included in a SQL statement construction via concatenation. What got passed to the database was 0.00018962
If you were assigning date parameter values, then you wouldn't need to do this, since the parameters are of known data type.
aikimark@...
21st Aug 2009
Thank you very much to all the replies
dilumd@...
26th Aug 2009
