Question Problems in View by Date

[gate7cy]

I am developing an application in VS studio, VB, using access 07. I have a datagridview with a view by date datetimepicker. I have this query created in dataset designer which does not work

SELECT        s.ServiceID, c.CustName, c.Surname, s.CustomerID, s.SupplierID, s.ServiceName, s.Company, s.Service, s.Description, s.Solution, s.Parts, s.StartDate, 
                         s.EndDate, s.Technicians, s.Completed, s.TimeTaken, s.Dismiss, s.ToSale, s.TimeScheduled, s.Model, s.Serial, s.Peripherals, s.UserID, s.LastEditBy
FROM            (Services s LEFT OUTER JOIN
                         Customer c ON s.CustomerID = c.CustomerID)
WHERE        (s.StartDate = ?)

I load it by this command

 Me.AllServicesTableAdapter.testdate(Me.Database2DataSet.AllServices, (Me.DateTimePicker1.Value))

I do not what goes wrong as I am using this method to filter my data throughout my program and only here I find problems. Thank you for you time

 

[cjard]

Dates are stored as decimal numbers representing the number of days since some point in time

Suppose 0 = MIDNIGHT Jan 1 1970

If your date is stored as SIX OCLOCK IN THE EVENING Jan 2 1970, then it has a numerical value of 1.75 because Jan 2, 1800hrs is 1.75 days after Jan 1, 0000hrs


Hence, I wouldnt mind betting that your start date is stored with/without a time component and youre searching without/with a time component respectively

e.g. You have a record on MIDNIGHT jan 2, so the StartDate = 1
And your datetime picker, you didnt realise, has a date of Jan 2 but a time of 1800hrs

You just asked the database for all rows where StartDate = 1.75

1 is not equal to 1.75


Either:
Specify a range (StartDate >= AND StartDate < ? ... and your parameters: dtp.Value.Date, dtp.Value.Date.AddDays(1) )
or
Ensure you truncate the time off both the stored value (when you store it - try DatePart() ? ) and the datetimepicker (try .Value.Date)

 

[gate7cy]

I have resolved this by the help of another forum by changing my query to a accept two dates and use between.

 

[InertiaM]

Expanding on cjard's 2nd suggestion, I always match the whole date in my queries.

SELECT
   ...
FROM
   Services s LEFT OUTER JOIN Customer c ON s.CustomerID = c.CustomerID
WHERE
  DATEPART (year, s.StartDate) = DATEPART (year, @STARTDATE)
AND
  DATEPART (month, s.StartDate) = DATEPART (month, @STARTDATE)
AND
  DATEPART (day, s.StartDate) = DATEPART (day, @STARTDATE)

 

[cjard]

Expanding on cjard's 2nd suggestion, I always match the whole date in my queries.

SELECT
   ...
FROM
   Services s LEFT OUTER JOIN Customer c ON s.CustomerID = c.CustomerID
WHERE
  DATEPART (year, s.StartDate) = DATEPART (year, @STARTDATE)
AND
  DATEPART (month, s.StartDate) = DATEPART (month, @STARTDATE)
AND
  DATEPART (day, s.StartDate) = DATEPART (day, @STARTDATE)

SQLServer is so lame..

In Oracle we'd just create a function based index on TRUNC(s.StartDate) and then our WHERE TRUNC(date) = TRUNCstartDate) uses an index..