SQL(Pretty hard one)

[CoMMiE]

Hi all
Anyone out there knows any SQL for comparing dates?
for example if i type in 1/1/2000 and 1/12/2000 it will display all the record between those date


Data1.RecordSource = " select productname,month, count(productcodeno) as count, SUM(orderdeliver)AS sum from customerpricelist where month = #" & strDate & "# and #" & strdate2 & "# group by productname,month"


Data1.Refresh



That is the current code i have but it fails to do the job

[honeybee]

Actually, I had posted the reply to a similar question somewhere else, might as well repeat myself.

CoMMiE, what you need to do is: convert strDate to a format, say 'mm/dd/yyyy' using the Format() function. Then in your query, convert the date field into the same format using the SQL date functions. I don't have any experience working with SQL, so I shall quote Oracle example:


Select * from Customers where TO_CHAR(BirthDate, 'mm/dd/yyyy') = '" & Format(MyDate, "mm/dd/yyyy") & "'"


See if it works.

By the way, you haven't answered my question, I think. Are you non-US ?

[CoMMiE]

Well honeybee thanks for the reply again!!!!!

By the way i am using a text box in VB to let the user enter the date so it is unecessery for me to convert the textbox format

Yeah i am a Non-US good guess thought

[Ianpbaker]

Hi commie

You can use The BETWEEN comparison or the IN comparison

SELECT *
FROM tbltest
WHERE datet BETWEEN #1/2/2000# AND #1/3/2000#

Will return anything fields in that date range

SELECT *
FROM tbltest
WHERE datet In (#1/2/2000#,#1/3/2000#)

Will return any fields that have the date's put in. It is a quicker way of rightinh datet = #1/2/2000# OR datet = #1/3/2000#

Hope this helps

Ian

[CoMMiE]

Thanks for the reply Ianpbaker
But the sugestion you sugest give me a wrong result for example when i key in the date 1/1/1999 and 3/1/1999 it came out the query which is for 2/3/1999 which is totally out of the target

here is my updated code below


Data1.RecordSource = " select * from customerpricelist where month between #" & strDate & "# and #" & strdate2 & "#"
Data1.Refresh


i check and analyse the above code many times but i still get the same result

can anyone out there point out what have i done wrong

[vandyck_anthony]

The reason for not getting the right output is because your system is using the International dating system. Switch to an English-American dating system and try the reponses from before. Or Think about this 1/1/1999 - 3/1/1999 are these dd/mm/yyyy? and if so then 2/1/1999 would have been the correct output. Hope tis helps.
Anthony

[CoMMiE]

well thanks a lot vandyck_anthony :)
you have finaly pointed out the problem, it all started because SQL assume i am using the AMERICAN date system while all the while i have been following the BRITISH date standard.

so the question now is how can i make the SQL compare dates in dd/mm/yyyy instead of the AMERICAN mm/dd/yyyy

i cant belive the date standard have cause so much headeche for me :)

[Ianpbaker]

Hi Commie

Go into control panel and click on regional settings and make sure it set to english(Brittish) then click on the date tab and it should show dd/mm/yyyy.

This should sort out your problem

Ian

[CoMMiE]

Thanks a lot Ianpbaker
By setting the windows reginal setings it seems that it only solve half of the problem, after i experiment more with the query i found out that the SQL has follow the BRITTISH date format but the main problem now is VB is still folowing the AMERICAN date format!!!!!!

So the question now is can i do anything so that VB will assume that i am following the BRITTISH date format ?

[vandyck_anthony]

you can use the syntax of
Format(#dd/mm/yyyy#) anytime
esp when doing a conversion
or comparison of data.
Hope this helps...
Anthony