Web Development

Ask something
Discussion Locked

Date format of VB select data from VFP

0 helpful
8 replies

by poois

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

8 replies

by jimbb

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

by poois

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

by donq

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.

by poois

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

by mark.thomson

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

by poois

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

by spankil

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

by spankil

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

This discussion is closed to new replies.