Getting Start & End of Month for 12 Months (A Year)

Question

I'm actually creating a method to get details for my asp Chart based on a certain datetime range. I've looked at these 2 links,

First Link , Second Link

My method to retrieve data,

//Monthly Statistics
public int janLogin(String username)
{
    int result = 0;

    StringBuilder sqlCmd = new StringBuilder();
    sqlCmd.AppendLine("SELECT COUNT(*) FROM AuditActivity WHERE Username = @getUsername AND DateTimeActivity BETWEEN @getFirstDT AND @getLastDT AND ActivityType = @getType");

    try
    {
        SqlConnection myConn = new SqlConnection(DBConnectionStr);

        myConn.Open();

        SqlCommand cmd = new SqlCommand(sqlCmd.ToString(), myConn);

        //DateTime
        DateTime currentDT = DateTime.Today;

        //Code Below gets only current Start and End of Current month
        DateTime FirstDT = currentDT.AddDays(1 - currentDT.Day);
        DateTime SecondDT = FirstDT.AddMonths(1).AddSeconds(-1);

        cmd.Parameters.AddWithValue("@getUsername", username);
        cmd.Parameters.AddWithValue("@getFirstDT", FirstDT);
        cmd.Parameters.AddWithValue("@getLastDT", SecondDT);
        cmd.Parameters.AddWithValue("@getType", "Login");

        result = Convert.ToInt16(cmd.ExecuteScalar());

        myConn.Close();

        return result;
    }
    catch (SqlException ex)
    {
        logManager log = new logManager();
        log.addLog("AuditNLoggingDAO.janLogin", sqlCmd.ToString(), ex);
        return 0;
    }
}

What i'm trying to actually do as you can see from the method name janLogin basically to get how many people logged in from 1 Jan 2017 00:00:00 to 31 JAN 2017. Current month is actually August. How can i actually get for all twelve months (Jan to Dec) the start and end month (from the bold example) for current year of 2017?

If i'm not wrong it's having to involve a for loop? Where the end count is 12. But i'm quite sure on how to do it.. There might be a duplicated question but i cant seem to find one that fits my requirement..

Appreciate any help. Thank you!

Answer 1

The entire 2017 calendar year

The whole calendar year:

int year = 2017
DateTime FirstDT = new DateTime(year, 1, 1); // jan 2017
DateTime SecondDT = FirstDT.AddYear(1).AddSeconds(-1); // last second of dec 2017

The example above will return 01 Jan 2017 00:00:00 to 31 Dec 2017 23:59:59
Note the issue with precision at the bottom of this answer for 31 DEC 2017 23:59:59.999999

---

A particular month in a year

In order to get a different month, instead of using the current one, you can do it like this:

int year = 2017;
int month = 12;
DateTime FirstDT = new DateTime(year, month , 1); // december 2017
DateTime SecondDT = FirstDT.AddMonths(1).AddSeconds(-1); // last second of dec 2017

The example above will return 01 Dec 2017 00:00:00 to 31 Dec 2017 23:59:59
Note the issue with precision at the bottom of this answer for 31 DEC 2017 23:59:59.999999

Then you can alter your method to take the month as a parameter like so:

public int LoginsByMonth(int year, int month, String username)
{
    if (month < 1 || month > 12)
    {
        throw new ArgumentOutOfRangeException("month must be between 1 and 12.");
    }

    ...
    
    DateTime FirstDT = new DateTime(year, month, 1);
    DateTime SecondDT = FirstDT.AddMonths(1).AddSeconds(-1);

    ...
}

and you can call your method like this as singles:

 int loginsNovember2017 = LoginsByMonth(2017, 11, "userA");
 int loginsDecember2017 = LoginsByMonth(2017, 12, "userA");

---

Precision: Using Ticks instead of Seconds

As noted in the comments below. You could use .AddTicks(-1L) instead of .AddSeconds(-1) to get a more accurate filter. However, depending on your requirements you may prefer to go with avoiding this altogether with the next option.

Avoiding precision

Also as noted in the comments below. You could avoid this precision headache alltogether by switching up your SELECT stament to include a < (less than) condition in your WHERE statement instead of worrying about the precision when using the BETWEEN condition.

The changes would look like this

SELECT COUNT(*)
FROM AuditActivity
WHERE Username = @getUsername 
    AND DateTimeActivity >= @getFirstDT
    AND DateTimeActivity < @getLastDT
    AND ActivityType = @getType

Which means you can drop the AddSeconds(-1) or AddTicks(-1L). For example:

// entire year
DateTime SecondDT = FirstDT.AddYear(1);
// month
DateTime SecondDT = FirstDT.AddMonths(1);

Answer 2

So, I guess there are two different questions here.

The first one is "How to get a date range for a specific month by year and month?"

Here's a way:

var year = 2017;
for (int month = 1; month <= 12; month++)
{
    var firstDayOfMonth = new DateTime(year, month, 1);
    var lastDayOfMonth = new DateTime(year, month, DateTime.DaysInMonth(year, month));
    Console.WriteLine($"{firstDayOfMonth} - {lastDayOfMonth}");
}

Keep in mind that there is no Date-only values of DateTime. If you omit the time part in the constructor like I did, it will be set to the midnight of that day. When using these values to filter some dates, you should either call .Date on it first:

var datesInThisMonth = someDates.Where(x => x >= firstDayOfMonth && x.Date <= lastDayOfMonth);

...or use strict less with the first day of next month as an upper bound:

var datesInThisMonth = someDates.Where(x => x >= firstDayOfMonth && x <= lastDayOfMonth.AddDays(1));

And the second one is how to use that value to build an SQL query.

You should not use BETWEEN here. Depending on what data type you use for the column it's either hard to digest or just plain wrong.

Here's the alternative you have:

• Your column is of datetime type

Then it's rounded to .000, .003, .007 seconds. So, the upper bound you will use in your BETWEEN statement will be rounded to either 23:59:59.997 or 00:00 of the next day. You will either miss some records, or include some records multiple times (it will be both in the results for this month and for the next one).

• Your column is of datetime2 type

Then it could be possible to write the correct query, but again in order to do it right you'll have to consider what precision do you use in SQL (it could be customized for that specific column of this specific table) and what value should you pass from C# to get the right value in SQL.

I don't see how it's worth the hassle.

Just use x.Date > firstDayOfMonth AND x.Date < firstDayOfNextMonth and forget about it.

You can also take a look at this answer.

Answer 3

Why don't you just use the function that SQL gives you?

@"SELECT MONTH(DateTimeActivity), COUNT(*) FROM AuditActivity 
  WHERE Username = @getUsername AND ActivityType = @getType
  GROUP BY MONTH(DateTimeActivity)
  ORDER BY MONTH(DateTimeActivity)"

This will give you 12 records with count for each month (assuming you have dates for all year's months) with just ONE single call to the database and not 12 different calls inside a loop. And the WHERE statement is a lot simpler with no calculations on the DateTimeActivity field

This approach has just one problem. It doesn't return a record with zero value if the WHERE statement doesn't find any record with the given constraints.

But in code is relatively easy to handle the eventual missing months

    cmd.Parameters.AddWithValue("@getUsername", username);
    cmd.Parameters.AddWithValue("@getType", "Login");

    SqlDataReader reader = cmd.ExecuteReader();
    int[] monthCount = new int[12];
    while(reader.Read())
        // -1 because arrays start at zero and January has value 1.
        monthCount[reader.GetInt32(0) - 1] = reader.GetInt32(1);

At the end you have an array of integers where each element represents the count of each month with zero for months with no records after the givens constraints in the WHERE statement are applied.

Answer 4

This should get the logins for the current year as an array of integers representing each month):

public int[] yearLogin(String username)
{
    string sqlCmd = @"SELECT COUNT(*)
FROM AuditActivity
WHERE Username = @getUsername AND DateTimeActivity
BETWEEN @getFirstDT AND @getLastDT AND ActivityType = @getType";

    try
    {
        Func<SqlConnection, DateTime, DateTime, int> fetch = (c, f, t) =>
        {
            using (SqlCommand cmd = new SqlCommand(sqlCmd.ToString(), c))
            {
                cmd.Parameters.AddWithValue("@getUsername", username);
                cmd.Parameters.AddWithValue("@getFirstDT", f);
                cmd.Parameters.AddWithValue("@getLastDT", t);
                cmd.Parameters.AddWithValue("@getType", "Login");

                return Convert.ToInt16(cmd.ExecuteScalar());
            }
        };
        using (SqlConnection myConn = new SqlConnection(DBConnectionStr))
        {
            myConn.Open();

            DateTime currentDT = DateTime.Today;
            DateTime FirstDT = currentDT.AddMonths(1 - currentDT.Month).AddDays(1 - currentDT.Day);

            int[] result =
                Enumerable
                    .Range(0, 12)
                    .Select(x => fetch(myConn, FirstDT.AddMonths(x), FirstDT.AddMonths(x + 1).AddTicks(-1L)))
                    .ToArray();

            return result;
        }
    }
    catch (SqlException ex)
    {
        logManager log = new logManager();
        log.addLog("AuditNLoggingDAO.janLogin", sqlCmd.ToString(), ex);
        return null;
    }
}