SQL Server Compact 4.0 DateTimePicker select query between dates

Question

Using Winforms, .Net 4.5.

I have a form with a DataGridView and in the dataGridView I insert a few values from another form like Client name and Date, I struggled for a few hours to figure it out, I'm using SQL Server Compact 4.0 so it only supports DateTime datatype unlike SQL Server 2008 or so where you can store date without time, so what I need to do is some select between dates query.

But in SQL Server Compact you can store only the whole value of date like 24/02/2014 12:00 I only need the date and SQL Server Compact won't let me do that. I tried using DateTimePicker.Text to send data to SQL Server Compact but with strings you cannot do a select between dates because they are not dates they are strings. What can I do from this situation, remember I need only date format I don't need the time, remember also SQL Server Compact cannot store only dates. So is there any possible ways to do this select between strings (dateTimePicker.Text) ?

Answer 1

Store DateTime in your database - you really have no other choice.

If you don't need time - use only Date portion of DateTime when performing selects.

Edited:

I'm not sure how you do your selects, but if it is just text query then

WHERE clause would look like this:

"DateColumn >= '" + sd.ToShortDateString() + "' AND DateColumn <= '" + ed.ToShortDateString() + "'";

where sd is a start date and ed is the end date, both of type DateTime in C#, and DateColumn is the column where you keep your dates. Same idea applies for inserts.

SELECT would look like this (returns 12/12/2013):

select ltrim(str(DATEPART(month, DateColumn))) + ':' + ltrim(str(DATEPART(day, DateColumn))) + ':' + ltrim(str(DATEPART(year, DateColumn))) AS MyDate FROM TableName

Generic use of DATEPART:

SELECT DATEPART(month, [DateColumn]) AS Month FROM TableName

More on DATEPART is here

Also, see this post and this post