DateTimePicker c# only works with Months

Question

Help as early as possible please I have a form in which I add Exhibitions in the museum name, the date through the Datetimepicker and people came up and then they are saved in the sql database And then on another form I want to select 2 dates in two Datetimepickers and to show in the datagrid that there are some exhibitions between two dates BUT it does not show me correctly, I can not even explain whats wrong

private void button3_Click(object sender, EventArgs e)
{
string period = "SELECT* FROM vistavka WHERE Date BETWEEN '" + dateTimePicker1.Value.ToString() + "' AND '" + dateTimePicker2.Value.ToString() + "'";

LoadData(period);
dataGridView2.Columns[0].Visible = false;
/*dataGridView2.Columns[1].Visible = false*/;
dataGridView2.AllowUserToAddRows = false;
button1.Enabled = true;
}

is it right? i want it show like events between 12.November.2020 and 21.December.2020

you can simply use [DateTimePicker.Format](https://learn.microsoft.com/en-us/dotnet/api/system.windows.forms.datetimepicker.format?redirectedfrom=MSDN&view=net-5.0#System_Windows_Forms_DateTimePicker_Format) to set the format that you to your picker — styx, Dec 28 '20 at 08:46
if you are using string, then N is greater than D, so for example 12.N is grater than 12.D, but 11.N is less than 12.D, because eleven is smaller than 12, and it's done character by character. also number of char are matter, for example x between 'a' and 'c', only check for one character words including 'a', 'b', 'c'. you should first store your date in DateTime, and use date.ToString(format) look for format in MSDN, to display your data, and use DateTime.Parse to convert string to DateTime value. then you can use x>=startDate AND x<= endDate — Hassan Faghihi, Dec 28 '20 at 08:57

Dan Csharpster · Answer 1 · 2020-12-28T15:08:06.400

1

I think you can just use the answer from this question here.

Basically, you just need to feed it the proper format:

string period = "SELECT* FROM vistavka WHERE Date BETWEEN '" + dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss.fff") + "' AND '" + dateTimePicker2.Value.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'";

For bonus points you could also switch to using string interpolation to make it more readable.

string period = $"SELECT* FROM vistavka WHERE Date BETWEEN '{dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss.fff")} ' AND '{dateTimePicker2.Value.ToString("yyyy-MM-dd HH:mm:ss.fff")} '";

Update:

Please check out this on why doing it this way is a security problem.
Then learn about SQL parameters here.
And finally you can see this answer for how to do date parameters. Basically, you shouldn't have to convert a C# datetime to a string just to pass it into SQL. C# already has mechanisms for this and for good reason, as you'll see in those links.

edited Dec 28 '20 at 15:08

answered Dec 28 '20 at 14:55

Dan Csharpster

2,662
1
26
50

1

For even more bonus points you can fix the SQL injection issue and the `SELECT *` issue – Charlieface Dec 28 '20 at 14:56
Haha! Well yes, that too. They should be using parameterized SQL and sanitizing their inputs, but I was trying to take it one step at a time and just answer the question that was asked and not overwhelm them. But since we're on the topic, here's some reading material: https://learn.microsoft.com/en-us/archive/msdn-magazine/2004/september/data-security-stop-sql-injection-attacks-before-they-stop-you- https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-5.0 – Dan Csharpster Dec 28 '20 at 15:02
@Charlieface, I went ahead and updated my answer. Thanks for holding me to a higher standard. :-) – Dan Csharpster Dec 28 '20 at 15:08
1

Looks good. There is so much garbage SQL usage out there it feels like it's our duty to correct it, which is why I downvote bad questions and then answer them. – Charlieface Dec 28 '20 at 15:22
Makes sense. I usually take a bit gentler approach with new users though and skip the downvoting, as I want them to feel welcome and keep coming back to learn more. I've seen many downvoted questions get deleted before I or someone else can respond, mostly from people with < 50 reputation. I think some people feel bad for asking a dumb question and then run away, which prevents them from learning. – Dan Csharpster Dec 28 '20 at 15:35

oleksa · Answer 2 · 2020-12-28T14:50:58.700

the date to string convertion was used is wrong

"SELECT* FROM vistavka WHERE Date BETWEEN '" + dateTimePicker1.Value.ToString() + "' AND '" + dateTimePicker2.Value.ToString() + "'";

ToString() format is culture depended and should not be used

You have to pass SQL query with parameters and parameter values (as DateTime objects) to convert date from DateTime by SQL client API to the parameter value format used by your SQL server.

Please find MS SQL sample here

DateTimePicker c# only works with Months

2 Answers2