How to accurately pick between two date values?

Question

1.00/5 (1 vote)

See more:

Hello. I want to create a search for my entry dates and i wrote this line of code.

C#

private void History()
        {
            int i = 0;
            dataGridView1.Rows.Clear();
            cn.Open();
            cm = new SqlCommand("select * from vwStockIn where cast(sdate as date) between @sdate and @sdate1 and status like 'Bitti'", cn); 
            cm.Parameters.AddWithValue("@sdate", dt1.Value.ToShortDateString());
            cm.Parameters.AddWithValue("@sdate1", dt2.Value.ToShortDateString());

            dr = cm.ExecuteReader();
            while (dr.Read())
            {
                i++;
                dataGridView1.Rows.Add(i, dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString());
            }
            dr.Close();
            cn.Close();
        }

Now this is very wonky. Sometimes it shows me the time i want and sometimes it just shows everything, sometimes it gives an error because i put a day number higher than 12. Maybe its because i think of it as a europaen time thing because just when i click 13 and try to search it gives me the error following .

System.Data.SqlClient.SqlException: 'Conversion failed when converting date and/or time from character string.'

Even if i've gotten rid of this error there is still a huge problem.Its unreliable.It never shows me the thing i want but shows everything else.Is there a way that i can get consistent and accurate dates? I mean when i click 01:09:2020 - 10:09:2020 and search it i have a data in sql which is in 01:09:2020 but it doesnt show me that one but it shows me the one at 18:09:2020 like what the f.. is going on?

What I have tried:

I thought that maybe the problem was that i tried writing it with parameters and i tried writing it like this

C#

cm = new SqlCommand("select * from vwStockIn where cast(sdate as date) between '" + dt1.Value.ToShortDateString() + "' and '" + dt2.Value.ToShortDateString() + "' and status like 'Done'", cn);

Also i've tried writing it without having cast(sdate as date) line and replaced it with just sdate but that made no difference aswell

Posted 9-Sep-20 19:38pm

mekenix

Updated 9-Sep-20 20:03pm

Add a Solution

Comments

mekenix 10-Sep-20 1:53am

I think the problem is that microsoft sql expects dd,MM,yyyy value while visual studio c# gives MM,dd,yyyy value but i am not sure. Even if that was the problem i have no idea how to fix it so i need help :D

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-09-09T19:58:00

Never do this:

C#

cm = new SqlCommand("select * from vwStockIn where cast(sdate as date) between '" + dt1.Value.ToShortDateString() + "' and '" + dt2.Value.ToShortDateString() + "' and status like 'Done'", cn);

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Don't do this:

C#

cm.Parameters.AddWithValue("@sdate", dt1.Value.ToShortDateString());
cm.Parameters.AddWithValue("@sdate1", dt2.Value.ToShortDateString());

THat just introduces a layer of extra complexity as you convert to a string based on the your computer settings, and SQL converts back to a DATE based on its settings.
Instead, pass the DateTime value directly:

C#

cm.Parameters.AddWithValue("@sdate", dt1.Value);
cm.Parameters.AddWithValue("@sdate1", dt2.Value);

And no conversions are required.
Unless of course your DB column is storing date information in a VARCHAR or NVARCHAR column instead of DATE, DATETIME, or DATETIME2 in which case you need to change that or your comparisons will not work correctly anyway.

Then, look at the rest of your SQL:

C#

cm = new SqlCommand("select * from vwStockIn where cast(sdate as date) between @sdate and @sdate1 and status like 'Bitti'", cn);

LIKE is an operator that needs wildcards: without them it's identical to "=" but slower.
You could try:

SQL

...LIKE '%Bitti%

which will find anything with "Bitti" in it.

Then, this worries me:

C#

...WHERE CAST(sdateAS DATE)...

which implies that you are storing date info in a VARCHAR or NVARCHAR column, and that's a bad idea, because when you try to use it you have to convert it to a date to do anything - if you don't then a string based comparison is used, and that gives a result solely based on the first pair of different characters. Which means that sorting and comparing does not use date order: 01-12-2020 is before 31-01-2000. And ... it relies on your data never getting "bad data", which is unlikely, and which includes every computer inserting date info using the same settings ...
If you are, change your DB