Select all the dates in a year

Select all the dates in a year

  
How do I select all the dates in a year to perform a left outer join with the other table with the dates. I need to  get the list of dates that needs to be disabled that needs to be given in the calendar control. But all I have is the list of enabled dates like below

Please help me with this. I am really struck. Thanks


Hi v,

I'm not sure how wise it is to pass so many dates to the calendar control. That said, I take it you mean the SilkUI calendar? (The RichWidgets one doesn't seem to have a parameter for unselectable dates.) My advise would be to not try to do it in a query, but post-process it afterwards.

For one, using the first and last allowable date of your list of enabled dates, you could set the MinDate and MaxDate - which would already disable a lot of dates without explicitly specifying (so in your example above, 03/06/2015 could be the MinDate, etc.). Next you need to create a loop that runs over all dates between the min and max dates, check against the next date in your enabled list, and decide whether to block that date. Etc.
Is there any simple sample available please. Because the MinDate and MaxDate date are set up in the control itself right. May be I am missing something. Is there a way I can create a table with all the dates in 2015 and 2016, I can use a Left join with the available SnapShotDate
v,

I'll say it again: creating a table with all the dates just so you can join it is not good practice. You do not want to do that. If you are half a programmer, you also don't need a simple sample, you need to read what I wrote and if you don't understand, ask a directed question. I gave you a solution (what I deem the best solution), you ignore my comments and ask again about a table. I'll say it again, you don't want a table for that.
I am sorry Kilian, I will set up the Min Date as 01-01-2015 and Max Date as 12-31-2016. Because in production the snapshot dates might be different.Are you saying about the Min and Max Dates that is in the Silk UI calendar control or asking to create local variables with the min and max date with default value.  I was little confused with loop runs over all dates between min and max dates, thats the reason I asked if I need to create the table will all the dates.Please help me out with this.

Currently for testing purpose I tried to give the  dates in the snapshotdates tables as (yyyy-mm-dd,yyyy-mm-dd) in to SilkUI Calendar control as


Are you asking me to set the Max and Min Date in the Calendar control here.But if I am setting it here how do I run the loop, I am just little confused just that part. so to generate the rest of the dates in the calendar apart from the dates in SnapShotdates.
Hi v,

I'll try to explain again. If I understand correctly, you have a table that contains all dates that are Valid for a given period. If you sort the dates in the query, the first date is the Min Date and the last date the Max Date (all other dates aren't valid).

Next you need a list of invalid dates, as that's what the calendar needs (as DisabledDates). In my opinion, you shouldn't use a query to solve this. What you need to do, is construct a loop that loops over all days from Min Date to Max Date. You need a local variable CurrentDate or the like (of type Date, obviously). You need to construct a loop (with an If, not a ForEach) that increases the CurrentDate with one day, until it reaches Max Date. You will also need an index into the record list of the query result. You then test the CurrentDate against the Record from the record list at the index - if it's the same, the date is permissable, otherwise it's not and you add the date to the string containing the DisabledDates.

I attached an example so you can examine it. Note the example does not use a query, as I do not have a database with dates, but you should be able to modify the logic so you can embed it in your own program, if so desired.
Thank you so much, now I understand it better. I was really confusing with the Min and Max Dates in the widget calendar control.
Just one doubt as I am having the records (that is the snapshot dates in the entity), is it possible to use without the ForEach. I am tried to implement in my scenario with just using an IF condition but i doesnot work in my case.
I'm not sure what you mean. Which ForEach are you refering to?
What I am trying use a Foreach to verify each records Date is equal to the CurrentDate , which wat gets incremented. Since you were using a list we used
Index < GetEnabledDates.EnabledDates.Length and GetEnabledDates.EnabledDates[Index] = CurrentDate
to compare with the CurrentDate. But in my case they are records

GetSnapshotDates.List.Current.SnapshotDate.Date = CurrentDate
It works the same way, see attachment.
Thanks a ton. I am able to generate the list of disabled dates by giving mindate as 2015-01-01 and maxdate as 2016-12-31.As you said 


But just one issue like the Firstdate in the Snapshot 2015-03-06 but it appears in the Disabled Date list. I tried to debug it tries accept the 03-06-2015. And though I am using DateToText() function it is giving as 03/06/2015
DisabledDates + If(DisabledDates= "","",",") +NewLine() + DateToText(CurrentDate)


I am not able to export the oml for this. But otherwise it is omitting all the otherdates in DisabledDays
DateToText() always converts to yyyy-mm-dd, at least that's what help says. From the information you've provided I can't tell why that one date is in the disabled list.