General discussion

Locked

Date format of VB select data from VFP

By poois ·
Hi,
I am Using VB 6 to develop a program and using Visual FoxPro 6.0 as my database... I am facing a problem... when i select data by using sql (e.g. select * from table1 where date between sdate and edate) ( the date format that i am using is DMY) when i select those data in VFP's command window, the correct answer is loaded, but when i select data through my program in VB6 the result is not true.... for example ... i want to select data from (DMY) 01/01/2001 to 01/03/2001 .... the data from 01/01/2001 until 03/01/2001.... This give a a very big problem on select reports in my program... is anyone can help me about this??? Thank You Very Much

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Date format of VB select data from VFP

by JimBb In reply to Date format of VB select ...

I'm not really a programmer, so please don't shoot me ;-)

Isn't it possible to "create" your datestring, by putting the correct day and month numbers together in the correct order? Maybe "MyDate$=mid$(firstdate$,2,2)+"/"+left$(firstdate$,2)+"/"+right$(firstdate$,4)"? That could turn your 03/01/2001 (=firstdate$) into 01/03/2001 (=MyDate$)without having to rely on localised formats like ShortDate or so. The result you can use to compose your SQL string (...where date between "+MyDate$+" and "+MyOtherDate$).

Another solution is to doublecheck your Regional Settings of Windows. As far as I know VB uses those (like ShortDate and ShortTime and so). But that would mean that your application could behave differently, depending on how the PC is configured.

Jim

Collapse -

Date format of VB select data from VFP

by poois In reply to Date format of VB select ...

Jim,
Thank you.... i have tried this before... do u have any other solution?

Collapse -

Date format of VB select data from VFP

by donq In reply to Date format of VB select ...

Either your Windows Operating system software OR your Visual Basic setup options are set to English (M/D/Y) v.s European (D/M/Y) standards. You'll need to set all setup options to the same standard familiar to the users in the European country where you are working.

Collapse -

Date format of VB select data from VFP

by poois In reply to Date format of VB select ...

Thank you..... i have done this in the earlier stage..... do u have any other solution ?

Collapse -

Date format of VB select data from VFP

by mark.thomson In reply to Date format of VB select ...

When sending dates to _ANY_ SQL parser in VB _ALWAYS_ format your dates as dd-mmm-yyyy

your new sql will be built something like

SQLStr ="SELECT * from table1 WHERE Date _ between '" & format("dd-mmm-yyyy",sdate) _ & "' AND '" & format("dd-mmm-yyyy",edate) _
& "'"
where sdate & edate are of type DATE

Most SQL parsers seem to assume that dates will be in the form mm/dd/yy unless the first 2 digits > 12 so what you can get is random behaviour if you use dd/mm/yy to format your dates.The date format dd-mmm-yyyy is unambiguous as to which part represents days, which part represents months and which Century the date falls in (remember y2k anybody?)

Collapse -

Date format of VB select data from VFP

by poois In reply to Date format of VB select ...

Thank you... but i cannot use your solution , coz when i access to the VFP's database using sql, i have to use this format :{^yyyy-mm-dd} on date, this is to prevent y2k problem... :)

Collapse -

Date format of VB select data from VFP

by spankil In reply to Date format of VB select ...

try this out

Select * from Table where date between '" & format(dateserial(year(sdate), month(sdate), day(sdate)),"yyyy-mm-dd") & "' and '" & format(dateserial(year(sdate), month(sdate), day(sdate)), "yyyy-mm-dd")

Collapse -

Date format of VB select data from VFP

by spankil In reply to Date format of VB select ...

sorry the modified query is

Select * from Table where date between '" & format(dateserial(year(sdate), month(sdate), day(sdate)),"yyyy-mm-dd") & "' and '" & format(dateserial(year(edate), month(edate), day(edate)), "yyyy-mm-dd")

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums