Error when trying to get dates given a condition from Access database in WPF

Question

I'm developing a WPF application that saves user registers and dates into an Access database. I've had no problems inserting the dates, but now I want to retrieve with a DataReader some dates given a certain condition (e.g., all dates from the current month). When I try to read it through my application, the DataReader returns no rows. But when I try the same query on DBeaver, it does return data.

This reading problem only happens when I try to read dates given a certain condition as I said before, since if I try to read all the dates, there's no issues.

I've tried using a parameterized command and also directly inserting the condition with String.Format().

I currently have this function (which tries to calculate how many days in the month are left if we don't count the days saved in the DB):

public static int CalculateDaysLeftInMonth()
{
    int days = 0;
    List<DateTime> dates = new List<DateTime>();

    try
    {
        if (dbConnection.State != ConnectionState.Open)
            dbConnection.Open();

       OleDbCommand dbCommandQuery = new OleDbCommand {
            CommandText = "SELECT * FROM DatesTable WHERE Date LIKE @Condition"
        }

        dbCommandQuery.Parameters.AddWithValue("@Condition", String.Format("{0:yyyy-MM}-%", DateTime.Now));

        OleDbDataReader dbReader = dbCommandQuery.ExecuteReader();

        if (!dbReader.HasRows)
            return -1;

        while (dbReader.Read())
        {
            dates.Add(new FechaFestivo(dbReader.GetDateTime(0).Date));
        }

        dbConnection.Close();

        DateTime startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1),
                 endDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 
                           DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month));

        for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
        {
            if (!dates.Any(ff => ff.Date == date.Date))
            {
                days++;
            }
        }
    }
    catch (OleDbException ex)
    {
        dbConnection.Close();
        return -1;
    }

    return days;
}

This is the parameterized version. I've also tried using:

dbCommandQuery.CommandText = String.Format("SELECT * FROM DatesTable WHERE Date " +
"LIKE '{0:yyyy-MM}-%'", DateTime.Now);

I expect to get a List of dates like this, so I can iterate through them:

list of dates on DBeaver

(CalendarioLaboral would be DatesTable and FechaFestivo would be Dates)

Thank you in advance.

If check here: https://stackoverflow.com/questions/1629050/sql-server-datetime-like-select the LIKE is not Supported for DateTime (and it makes no sense why it should), but you can convert to VARCHAR, if you want to use it. — Holger, Oct 08 '19 at 07:29
@Holger Thank you! I tried using this: `dbCommandQuery.CommandText = String.Format("SELECT * FROM {0} WHERE (DATEPART(yyyy, FechaFestivo) = {1:yyyy} AND DATEPART (mm, FechaFestivo) = {1:MM})", TablaCalendario, DateTime.Now);` but it gives me an OleDbException (Didn't specify values for some of the needed parameters). Should it be written in another way? Thank you again! — Biel, Oct 08 '19 at 07:57
You better supply the generated CommandText, after string.Format execution. This seems to be a pure SQL question and has nothing to do with C# at all. I'm not sure what TablaCalendario is, maybe it's ToString() method is producing something strange. — Holger, Oct 08 '19 at 08:32
@Holger The generated CommandText is `"SELECT * FROM CalendarioLaboral WHERE (DATEPART(yy, FechaFestivo) = 2019 AND DATEPART (mm, FechaFestivo) = 10)"`, and TablaCalendario is just a string containing the name of the table. Sorry for all the inconveniences, and my issue is precisely that in my C# code this query doesn't work, but in an SQL client like DBeaver it does... Though if this question isn't in the right place, I'll edit the tags. — Biel, Oct 08 '19 at 08:39
The `mm` and `yyyy` parameters need to be enclosed in apostrophe delimiters in generated SQL string. Must be `'yyyy'` and `'mm'`. — June7, Oct 08 '19 at 08:59
Thank you @June7 ! I also had to use `'m'` instead of `'mm'`. I'll publish an answer with the code. Thank you again for everything. — Biel, Oct 08 '19 at 10:03
Oops, sorry about 'mm' - it works in Format() function, got 'em mixed up. — June7, Oct 08 '19 at 10:16

score 1 · Answer 1 · answered Oct 08 '19 at 11:02

1

Like '2019-10-%', means a day in October 2019, so it is equivalent to date between 2019-10-01 AND 2019-10-31. Try like:

SELECT * FROM DatesTable WHERE Date between '2019-10-01' AND' 2019-10-31'

answered Oct 08 '19 at 11:02

apomene

14,282
9
46
72

Biel · Accepted Answer · 2019-10-08T10:55:56.987

The code that worked for me in the end is the following one:

public static int CalculateDaysLeftInMonth()
{
    int days = 0;
    List<DateTime> dates = new List<DateTime>();

    try
    {
        if (dbConnection.State != ConnectionState.Open)
            dbConnection.Open();

       OleDbCommand dbCommand = new OleDbCommand {
           CommandText = String.Format("SELECT * FROM {0} WHERE (DATEPART('yyyy', FechaFestivo) = @Year " +
                                       "AND DATEPART ('m', FechaFestivo) = @Month)", TablaCalendario)
       }

       dbCommandQuery.Parameters.AddWithValue("@Year", DateTime.Now.Year);
       dbCommandQuery.Parameters.AddWithValue("@Month", DateTime.Now.Month);

        OleDbDataReader dbReader = dbCommandQuery.ExecuteReader();

        if (!dbReader.HasRows)
            return -1;

        while (dbReader.Read())
        {
            dates.Add(new FechaFestivo(dbReader.GetDateTime(0).Date));
        }

        dbConnection.Close();

        DateTime startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1),
                 endDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 
                           DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month));

        for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
        {
            if (!dates.Any(ff => ff.Date == date.Date))
            {
                days++;
            }
        }
    }
    catch (OleDbException ex)
    {
        dbConnection.Close();
        return -1;
    }

    return days;
}

Using the DatePart function as said @Holger and @June7 in the comments. Thank you!

Error when trying to get dates given a condition from Access database in WPF

2 Answers2