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?

Recommended Answers

All 9 Replies

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.

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

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

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;
        }

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?

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?

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?

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.