SQL Getting it all wrong.... i think...

Question

3.50/5 (2 votes)

See more:

Here is my relevant code:

C#

public DateTime today = DateTime.Now;
public void filllist()
        {
            myCommand = "Select SiteCodeAndDate from Shifts where Date < '"+today+"'";
            SqlCeConnection con = new SqlCeConnection(connect);
            SqlCeCommand com = new SqlCeCommand(myCommand, con);
            try
            {
                con.Open();
                SqlCeDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Items.Add(reader[0]);
                }
            }
            catch (Exception p)
            {
                MessageBox.Show(p.ToString());
            }
        }

So, i would like the list to be filled with appointments after today, and none from previous days to be shown. Heres where it gets odd, at the time of this posting, it is 30/10/2013. Now, i have an appointment in the database for 31/10/2013, and one for 1/11/13. When i use

C#

where Date < '"+today+"'"

it shows the appointment from the 1/11/2013, whereas if i change the < to a > it shows the 31st appointment. Any ideas anyone??

Posted 29-Oct-13 20:34pm

brucey_D

Add a Solution

4 solutions

Solution 1

Yes. Stop using string concatenation to assemble SQL commands...
When use concatenate strings (ignoring the potential for SQL Injection attacks and the damage they can do to you database) you pass data to SQLCE in a format that it has to guess what you are talking about, and try to work out the date format. That probably isn't causing your problem here, but it's worth using parametrised queries anyway:

C#

myCommand = "Select SiteCodeAndDate from Shifts where Date < @DT";
SqlCeConnection con = new SqlCeConnection(connect);
SqlCeCommand com = new SqlCeCommand(myCommand, con);
com.Parameters.AddWithValue("@DT", today);

Means that the date is passed in correctly and can never be misinterpreted.

Now, to the problem to are specifically meeting:

Why did you make the database field "Date" a text based column? Why didn't you make it DateTime? That way the comparison would have been between two DateTime objects instead of two strings. And string comparisons are based on looking at each character in turn until you find a difference and useng that as the whole comparison.
So, "date < today":

31/10/2013
30/10/2013    LOWER:  '0' is lower than '1'

1/11/2013
30/10/2013    HIGHER: '1' is lower than '3'

See what I mean?

Posted 29-Oct-13 21:07pm

OriginalGriff

Solution 2

please get only record so that you will get appropriate value.For this
1. Please use min /max or top 1 in your query so that you will get appropriate result.
2. Dateformat of parameter should be in proper format so convert your date to "yyyy/MMM/dd" like..
e.g Select max(dtdate) from Person
where dtdate <'2013/Dec/10'
3. Try to use funcations like datediff also.

Posted 29-Oct-13 21:17pm

sp_suresh

Solution 4

Use This...

"Select SiteCodeAndDate from Shifts where Date < Convert(DateTime,'"+today+"')";

Posted 31-Oct-13 16:35pm

Anudeep Jaiswal - MCA

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)

Dipesh Wadhwa · Accepted Answer · 2013-10-29T21:23:00

Solution 3

Modify your SQL command as follows

C#

myCommand = "Select SiteCodeAndDate from Shifts where Date > getDate()";

getDate() is an Sql Funtion that gets current syatem date and time/

Posted 29-Oct-13 21:23pm

Dipesh Wadhwa