What data type should I use for reading year-month and year-week values ? c#

Question

Technology is winforms, using c#, database is SQlite.

I'm taking values from the DB, transfering them into lists, and subsequently populating a listview with those lists.

I have two columns for dates. One has values with the format of Year and month (YYYY-MM) and the other is Year and Week (YYYY-WW).

Problem:

But in the listview these values show up with slashes in between them, i.e January 2001 is written as 1/1/2001 12:00 am. The 31st week in 2002 is written as 3/1/2002 12:00 am.

I don't want the TIME or slashes in the output. (First 2 lists are the output in listview, with the errors i mentioned above. The second 2 columns are how the data is supposed to look).

What the problem is caused by: I use the DateTime datatype to store the values from these columns. Is there any datatype which will just output these values in year-month and year week format, instead of displaying slashes and time. If I try using int, the these columns don't even get read so can't use that.

Code: (Just focus on the yyyymm part)

 string sql6 = "select YYMM, TotalTrans  from t2 where cast(TotalTrans as int) < 1000";
       SQLiteCommand command3 = new SQLiteCommand(sql6, sqlite_conn);


       SQLiteDataReader reader3 = command3.ExecuteReader();

       while (reader3.Read())
       {

           int TotalTrans;
           DateTime yyyymm;  //Right here:

           if (DateTime.TryParse(reader3["YYMM"].ToString(), out yyyymm) && int.TryParse(reader3["TotalTrans"].ToString(), out TotalTrans))
           {
               YYMM.Add(yyyymm);
               TotalTransIrregularities.Add(TotalTrans);
           }
       }

Answer 1

For your YYMM column, you can use a DateTime like you are. When you output it as a string, you should use the yyyy-MM format.

Your YYWW column is more complicated, because DateTime doesn't support parsing weeks. I'd recommend that you create your own type, and display it in the ISO standard way of, e.g. 2002-W31. That type might look like this:

public struct YearWeek
{
    private static readonly Regex parseRegex =
                            new Regex(@"^(?<year>\d{4})-W?(?<week>\d{2})$");
    public int Year { get; private set; }
    public int Week { get; private set; }
    public YearWeek(int year, int week) : this()
    {
        this.Year = year;
        this.Week = week;
    }
    public static bool TryParse(string s, out YearWeek result)
    {
        var match = parseRegex.Match(s);
        if (match.Success)
        {
            result = new YearWeek(int.Parse(match.Groups["year"].Value),
                                int.Parse(match.Groups["week"].Value));
            return true;
        }
        else
        {
            result = default(YearWeek);
            return false;
        }
    }
    public static YearWeek Parse(string s)
    {
        YearWeek result;
        if (TryParse(s, out result))
            return result;
        else
            throw new ArgumentException("s");
    }
    public override string ToString()
    {
        return string.Format("{0:0000}-W{1:00}", Year, Week);
    }
}

Answer 2

You can use format string in DateTime.ToString(...) method. Use this article to compose your format string http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx . To get DateTime structure from custom datetime string use DateTime.ParseExact(...) method.

For example year-month format string will be "yyyy-MM"

As for the week number, the case is little more difficult. There is no week number format string but you easily can get it from date. See the answer for this question: Week number of a year in dateformat It has everything you need.

Answer 3

You can use ToString() function on DateTime Types to get the required format of the string.

Example:

DateTime dt=DateTime.Now;
dt.ToString("yyyy"); => gives you year => 2013
dt.ToString("MM");=>gives you month=>11
dt.ToString("dd");=>gives you Date=>19

Solution 1 : For getting Date in format of yyyy-MM

String yearmonth=yyyymm.ToString("yyyy-MM");

Solution 2: for getting the Date in format of yyyy-WW

inorder to get the WeekNumber you need to use Calendar. You can invoke GetWeekOfYear() method on Calendar object to get the Current Week Number.

using System.Globalization;
Calendar myCal = CultureInfo.InvariantCulture.Calendar;
DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
String yearweek = yyyymm.ToString("yyyy") +"-"+ myCal.GetWeekOfYear(yyyymm, dfi.CalendarWeekRule, dfi.FirstDayOfWeek).ToString();

Answer 4

Use the STRFTIME function in your query.

sqlite> select strftime('%Y-%m', 'now');
2013-11
sqlite> select strftime('%Y-%W', 'now');
2013-46