DateTime WHERE BETWEEN Query problem

[Djiber]

I have an Database Table containing columns, 2 columns of my problem concern are:
- column Date
- column Time

I'm using that Table to show data by DateTime

On my form I have:
- 4 dateTimePickers (2 of them for Date, 2 of them for Time)
- dataGridView
- button

On button click I would like to populate dataGridView with data that's between 2 DateTimes:

private void button1_Click(object sender, EventArgs e)
        {
            string Od, Do;

            Od = dateTimePicker1.Value.ToString("yyyy-MM-dd") + " " + dateTimePicker2.Value.ToString("HH:mm:ss");
            Do = dateTimePicker3.Value.ToString("yyyy-MM-dd") + " " + dateTimePicker4.Value.ToString("HH:mm:ss");

            dsu = new DataSet();

            string sqlu = "SELECT CAST(Racun.datum AS DATETIME) + CAST(Racun.vrijeme AS DATETIME) AS Vrijeme From Racun WHERE Vrijeme >='" + Od + "' AND Vrijeme <='" + Do + "'";
            
            dau = new SqlDataAdapter(sqlu, con);
            dau.Fill(dsu, "Hotel");

            dataGridView1.DataSource = dsu.Tables[0];

            foreach (DataGridViewColumn column in dataGridView1.Columns)
            {
                column.SortMode = DataGridViewColumnSortMode.NotSortable;
            }
        }

I don't know why but it seems it's ignoring WHERE part cause dataGridView is populated with all Data from Table

OR

It's treating Vrijeme as string

But when my SQL Query looks like this:

string sqlu = "SELECT CAST(Racun.datum AS DATETIME) + CAST(Racun.vrijeme AS DATETIME) AS Vrijeme From Racun WHERE Vrijeme ='" + Od + "'";

dataGridView is populated only with Data which has DateTime equal to Od;

EDIT: After some more combinations I've come to conclusion it's ignoring Date part in Od (So if I change Time part it'll show me all Data in that TimeSpan but as much I change Date I allways get all Data)

[Rabbit]

What you need to do is store your data in the correct format and not in strings.

But barring that, a workaround is to do something like this:

SELECT *
FROM someTable
WHERE CAST(dateString + ' ' + timeString AS DATETIME)
BETWEEN CAST(startDateString + ' ' + startTimeString AS DATETIME)
AND CAST(endDateString + ' ' + endTimeString AS DATETIME)

[Djiber]

My Columns in Table are date and time(0) and if you meant start/endDateTimeStri ng I don't understand why would it matter cause it's String in the Query anyway.

Problem was I've used Alias Column Vrijeme and thought it'll Compare with it, but it used Racun.vrijeme whole time(I didn't work with SQL for some time so I forgot simple things)

This:

SELECT (CAST(Racun.datum AS DATETIME) + CAST(Racun.vrijeme AS DATETIME)) AS Vrijeme From Racun WHERE (CAST(Racun.datum AS DATETIME) + CAST(Racun.vrijeme AS DATETIME)) BETWEEN '2012-09-11 00:00:00' AND '2012-09-19 10:40:08'

is working great.

Thank You for the help.

PS: Is it possible and how to use Alias Column in WHERE part so I avoid multiple CASTing of same things in bigger Query/Database.

[Rabbit]

Some DBMS allow you to do that but not SQL Server. You'd have to subquery it if you want to reference it by the new alias you gave it.

By correct data type, I mean store both as one field, a datetime field and not separate fields. The time data type is not really a time as much as it is a duration.

[Djiber]

Doesn't subquery use more resources.

I've tried to avoid them cause of that.

SELECT Vrijeme From (SELECT (CAST(Racun.datum AS DATETIME) + CAST(Racun.vrijeme AS DATETIME)) AS Vrijeme From Racun) WHERE Vrijeme BETWEEN '2012-09-11 00:00:00' AND '2012-09-19 10:40:08'

[Rabbit]

It depends on the subquery. A subquery that references a field outside it's local scope takes a lot of resources. But otherwise, they don't take many more resources. If all you're using the subquery for is to refer to something by a new alias, there's no need, just redefine it like you did in your previous query.