Hi All, i have a quick question i have all the items stored in a sql database, i have a function to load a this items basically i only pull down the items for the day on the calender(which is more efficient then pulling down all the items since the program has been used, my question is that when the planner is changed i pass in the date on the calender and get the items for that date. but if for example the item is a multiple day event and spans across 3 four days then when i move to the next day it will not be displayed because startdate is not that of the item.

mysql at moment is
"SELECT * from Item Where Unallocated = 0 And CAST(FLOOR(CAST(StartTime AS float)) AS datetime) = '" & MyDateVal & "'

this will not Draw items that cover multiple days for obvious reasons and the start date is not on all days coverd , have you seen this before can you suggest anything. 

thats works ok but as i move to another day it will not see anything be neither the start day or end day are contained within the 2nd day for example if you have an item spanning over 3 days them starttime would be on day 1 and endDate would be on day 3 when i use the direction arrows i call the getitems function be as neither the starttime or endtime fall within the days it will not see it.

sorry there is a typo in the code

for example if i run this query on the database 

SELECT * from Item Where Unallocated = 0 And CAST(FLOOR(CAST(StartTime AS float)) AS datetime) = '01-07-2008' Or CAST(FLOOR(CAST(EndTime AS float)) AS datetime) = '01-07-2008'  And DefaultDepot = '2' And DATEDIFF(d,'01-07-2008',CAST(FLOOR(CAST(StartTime AS float)) AS datetime)) >= 0 And DATEDIFF(d,'01-07-2008' ,CAST(FLOOR(CAST(EndTime AS float)) AS datetime))<= 0

the dates hard coded for the min it will return a record however if i go the next day for example the
'01-08-2008' where there should be a item because it crosses 3 days it returns no value and is not drawn on the planner

sorry for bothering you, i know what you mean by limiting to the items that start on the day but  i want to draw items that dont start on the day but run over the day and end in next day, so items that run through a full day, how can i put these on the planner using a query.

if i use

SELECT * from Item Where DATEDIFF(d,'01-07-2008',CAST(FLOOR(CAST(StartTime AS float)) AS datetime)) >= 0 And DATEDIFF(d,'01-07-2008' ,CAST(FLOOR(CAST(EndTime AS float)) AS datetime))<= 0 

for example the is fine for finding items that start on or before '01-07-2008' and end on or after '01-07-2008' but what about items on the same day/before that date  or even if i use the above statement and have a 3 day event it will not draw the 2nd day on the planner because the start date does not match sql query above



For Example

the below query will return all items with start date on or before  '01-07-2008' and finish on or after the '01-10-2008' 

i use the timetable view for the planner with the navigation buttons enabled so the use has a full view of the day, when form loads or  clicks on the nav button to go back a day or forward a day i call the get items function which gets the items for that day now if the item doesnt start on the day(just Passes Through) how do i draw it.


SELECT * from Item Where DATEDIFF(d,'01-07-2008',CAST(FLOOR(CAST(StartTime AS float)) AS datetime)) >= 0 And DATEDIFF(d,'01-10-2008' ,CAST(FLOOR(CAST(EndTime AS float)) AS datetime))<= 0 

nope i dont  although it goes down through the code and draws(i cant check this on planner cause only see one day but the code works) its when i click the nav button for the next day/back day it refreshes the planner and calls all the getItems again which will not draw the item because there are  no items in the planner with the current date(planner date) as start date i could hard code it but dont want to.

In MSSQL, if the first date argument of DATEDIFF is the earlier date, you get a positive result. If it is the later date you get a negative result. So, you should reverse the >= and <= positions. That's opposite to the MySQL DATEDIFF function (there you get date1 - date2), and I thought you referred to MySQL in your first post. So, to get items for which currentdate is later than startTime and earlier than endTime in MSSQL:

SELECT caption, startTime, endTime
FROM Items
WHERE DateDiff(d, currentdate, startTime) <= 0 AND DateDiff(d, currentdate, endTime) >= 0;

I am not that sure whether you should use "d" or "dy" as the first argument. Try with both to see which one gets correct results.

Stoyan