sql query (between datetimes)

[Mitja Bonca]

I would like to know why sql query for month and year is working, and is not working for day:

string myQuery = "SELECT StudentName FROM Students WHERE " +
                 "YEAR(Birth) BETWEEN @yearFrom AND @yearTo AND " +
                 "MONTH(Birth) BETWEEN @monthFrom AND @monthTo AND " +
                 "DAY(Birth) BETWEEN @dayFrom AND @dayTo;

As said, for year and month query works fine, but it doesn`t work for day. Why is that so?

[finito]

That looks fine, look at your code for errors. Maybe you are passing the Day out of index like 30 on a February.

Please post the error and if possible the code where you set day.

[Mitja Bonca]

There is no errors at all just the dataTable is empty. If I exclude the Day the dataTable gets populated.

[finito]

Can you try and do this in SQL Browser, see if you get response.

[Mitja Bonca]

This is my whole method:

private DataTable GetStudents(DateTime DateFrom, DateTime DateTo)
        {
            int MonthFrom = DateFrom.Month;
            int MonthTo = DateTo.Month;
            int DayFrom = DateFrom.Day;
            int DayTo = DateTo.Day;

            DataTable table = new DataTable("SelectedStudents");
            string TimeSearching = "SELECT StudentName, Birth FROM Students WHERE " +
                                    "MONTH(Birth) BETWEEN @MonthFrom AND @MonthTo AND " +
                                    "DAY Birth) BETWEEN @DayFrom AND @DayTo";
            SqlCommand cmd = new SqlCommand(TimeSearching, sqlConn);
            cmd.Parameters.Add("@MonthFrom", SqlDbType.Int).Value = MonthFrom;
            cmd.Parameters.Add("@MonthTo", SqlDbType.Int).Value = MonthTo;
            cmd.Parameters.Add("@DayFrom", SqlDbType.Int).Value = DayFrom;
            cmd.Parameters.Add("@DayTo", SqlDbType.Int).Value = DayTo;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(table);
            return table;
        }

[finito]

Why don't you just do

SELECT StudentName, Birth FROM Students WHERE Birth BETWEEN @DayFrom AND @DayTo

You need to pass it as yyyyMMdd

cmd.Parameters.Add("@DayFrom", SqlDbType.DateTime).Value = DateFrom.ToString("yyyyMMdd");

cmd.Parameters.Add("@DayTo", SqlDbType.DateTime).Value = DateTo.ToString("yyyyMMdd");

you get gist of it right?

[Mitja Bonca]

I was more thinking about this:

string myQuery = 
"SELECT StudentName FROM Students WHERE " +
"where (month(birth) * 100) + day(birth) between " +
"(month(@monthFrom) * 100) + day(@dayFrom) and " +
"(month(@monthTo) * 100) + day(@dayTo)";

What do you thing?

[finito]

Hmm, I don;t know if that will work.

But I see a syntax error WHERE where

WHERE " +
"where

But why not keep life simple and do it the way I told you?

[Mitja Bonca]

Yes, I did as you told me and it works. Bit thx mate.
Question answered.
best regards.
Mitja

[finito]

no problems glad to help
Can you mark this thread as solved if it is solved.