SQLCE: Find next available date

Question

I have a SQLCE table with a date field. Any given date could have one or more records. My client would like the input form to default to the next date (starting from and including the current date) that doesn't yet have a record. I'm having trouble wrapping my head around a query to accomplish this. Googling I've found a couple snippets, but they all use stored procedures or user defined functions which aren't available in CE. Is there way to do this without creating a loop in the code with multiple database calls?

Answer 1

I can't be sure that SQLCE allows all this syntax, but in T-SQL query like next would work:

select dateadd(d, 1, min(t1.mydatefield))
from mytable t1
left join mytable t2 on datediff(d, t1.mydatefield, t2.mydatefield)=1
where t1.mydatefield>=getdate() and t2.mydatefield is null