Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Aug 28, 2010 03:38 PM by sismeya
Aug 25, 2010 05:11 PM|LINK
Could someone help me , by explaining where I'm going wrong with this SQL Query of an Access Database which populates a Datagrid,
From a calendar selection the user can select a From and To Date and I want the SQL Query to ONLY pick up the dates between these two values, the selection is fine it seems to be the sql query which is the problem ... (I have added dates rather than the
text value to make it easier to get working ) the following query wil pick up dates outside of the selected values 01/08/2010 , 02/08/2010, etc
SELECT COUNT(*) AS Date_Installed, Date_Installed AS Expr1, Installed_By FROM Buildprocess WHERE (Date_Installed BETWEEN '20/08/2010' AND ' 25/08/2010') GROUP BY Date_Installed, Installed_By
Any Help would be geat !
Aug 25, 2010 05:41 PM|LINK
May I know what calendar control you are using? Are you directly passing the date value to SQL query?
I think the issue seems to be with the date format. SQL Server may encounter issue while converting the date you have mentioned.
If you try '2010-08-20' and '2010-08-25', it should work fine. You may need to convert the date from your calendar control and then pass it to SQL.
Aug 25, 2010 06:42 PM|LINK
Use parameterized queries:
and read this for more info about Access and Date(times)
Aug 26, 2010 09:42 AM|LINK
Thanks for your reply , I am usinging the Calendar Function within Visual Webdeveloper, but my biggest issue is the SQL Query, as its not returning the correct search values , as I said I sak for between "10/08/2010" and "19/08/2010" and I receive results
including July "07" , the results i am after are a list of Dates "Dates_Installed" displaying a Count of how many Installed by Each Engineer "Installed_By" - Between the Date Criteria...
So Far this gives me a very rough result
SELECT COUNT(*) AS Installed_By, Installed_By, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN '20-08-2010' AND '25-08-2010') GROUP BY Installed_By, Date_Installed
From Table "Build_Process
Aug 26, 2010 10:22 AM|LINK
Did you read the articles I referred to?
"It is not uncommon to find that people are trying to work with dates and times stored in TEXT fields. The problem with this approach is that any sorting on dates in TEXT fields will only be done alphabetically because they will be treated as strings."
My guess is that this is exactly what you're doing! So once again, read the articles, use datetime fields for storing dates and use parameterized queries....
Aug 26, 2010 10:29 AM|LINK
Yes I will read the article and apreciate I need to work this out myself but what i dont understand is why if I run the query with actual values in it as per my earlier message, it still returns incorrect values
SELECT COUNT(*) AS Installed_By, Installed_By, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN
'20/8/2010' AND '25/08/2010') GROUP BY Installed_By, Date_Installed
Aug 26, 2010 10:35 AM|LINK
Do you store the dates into Text fields?
In that case "21/07/2010" is larger as "20/08/2010" and smaller as "25/08/2010"
Aug 26, 2010 11:14 AM|LINK
Yes they are stored as Text Fields
Aug 26, 2010 11:23 AM|LINK
Yes they are stored as Text Fields
Exactly what I expected. The solution of your problem can be found in the articles....
Aug 26, 2010 07:11 PM|LINK
ok I now have the query working as per the links you kindly sent me , It works fine if I add manual dates into the but when I try adding the calendar date selection for the between 2 dates into the sql query I get a error :
Syntax error (missing operator) in query expression '(Date_Installed BETWEEN ([Date1]=?) AND ([Date2]=?) GROUP BY Installed_By, Date_Installed'
I am using the calender feature from within the Viusual Web Developer , the back end code I have is :
Sub calDate_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim dt1 As DateTime
dt1 = Convert.ToDateTime(CalDate.SelectedDate.ToString("d"))
Date1.Text = dt1
Sub calDate2_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim dt2 As DateTime
dt2 = Convert.ToDateTime(CalDate2.SelectedDate.ToString("d"))
Date2.Text = dt2
Can you help me , Is it because the selected date returns a value of say 17/08/2010 instead of 17/08/2010 00:00:00 ?