Question

Locked

VB 6 and SQL

By dilumd ·
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)

This conversation is currently closed to new comments.

15 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +
Collapse -

If you have fixed it, and it runs, then why change it?

by Peconet Tietokoneet In reply to VB 6 and SQL

You have just solved it, so why change it?

Collapse -

Because

by dilumd In reply to If you have fixed it, and ...

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

Collapse -

Best Date Format

by bruceanwyl In reply to Because

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.

Collapse -

Use Format()

by hineses In reply to VB 6 and SQL

Use the Format function on your date value e.g., format(dtOSdt, "yyyy-mm-dd").

Collapse -

Use ISO date format

by HGunter In reply to Use Format()

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.

Collapse -

Make the date format non ambigious

by darryl.muller In reply to VB 6 and SQL

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.

Collapse -

Agree with that... and more

by arvin_d In reply to Make the date format non ...

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) & "') ...

Collapse -

Best for me

by HarryJ In reply to Make the date format non ...

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.

Collapse -

Another way of formatting date

by azeemsiddiqui In reply to VB 6 and SQL

You can use VB Format() function as following:

Replace following part of your SQL

And ((stgrnmain.grndt >= '" & dtOSdt & "') and (stgrnmain.grndt <= '" & dtOEdt & "')))",

with

And ((stgrnmain.grndt >= '" & Format(dtOSdt, "yyyy-MM-dd") & "') and (stgrnmain.grndt <= '" & Format(dtOEdt, "yyyy-MM-dd") & "')))",

I hope it will also help.

Collapse -

However...

by arvin_d In reply to Another way of formatting ...

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) ...

Back to Networks Forum
15 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums