none
Formatting Numeric Value as Date

    Question

  • Here's another dandy one I have been struggling with.

    All my date fields are stored as numeric values as YYYYMMDD.  Doesn't .Net have a function that can convert this format to a datetime that can be formatted?  I have been writing string LEN functions to extract and format the field as MM/DD/YYYY but I am puzzled that I can't find a .net function to do this. 

    Hrmph.

    Saturday, December 10, 2005 7:26 PM

All replies

  •  J.H. Chrysler wrote:
    All my date fields are stored as numeric values as YYYYMMDD.  Doesn't .Net have a function that can convert this format to a datetime that can be formatted?
    Yes, it does, at least two in fact - ParseExact and TryParseExact.  TryParseExact is probably more efficient if you expect some number of failures but ParseExact gives you an exception that can be used to determine why the failure occurred.


    string strDate = "20051225";

    string strFormat = "yyyyMMdd";

    DateTime dt;

    try
    {
       dt =
    DateTime.ParseExact(strDate, strFormat, null);
       
    Console.WriteLine(dt);
    }

    catch (Exception ex)
    {
       Console.WriteLine (ex);
    }

     



    Instructions for constructing custom format strings can be found here.
    http://msdn2.microsoft.com/en-us/library/8kb3ddd4.aspx

    Sunday, December 11, 2005 11:33 AM
  • Frank,

    Wonderful!  Anyway to get this to work in a Gridview column?  I tried adding the record from a datatable with typeof DateTime but it states it can't convert my format of YYYYMMDD. 

    I've also tried adding it as typeof string and formatting the column with {0:d, yyyyMMdd} but this didn't work either.  My head is getting sore from all the wall-banging. 

    Here's is what I found that does work...Though I wonder if there's and easier way...

    First I converted the column from an int to a string like so:

    pmDataTable.Columns.Add("DATE_STR", typeof(string), "CONVERT(PM_PREV_DATE, 'System.String')");

    Then I did my formatting using the "stringed" column like so:

    pmDataTable.Columns.Add(
    "LAST_DONE", typeof(string), "SUBSTRING(DATE_STR, 5, 2) + '/' + SUBSTRING(DATE_STR, 7, 2) + '/' + SUBSTRING(DATE_STR, 1, 4)");

    Seems to work like a charm and leaves the column empty if it is null(0).

    One thing I also find interesting is that the Expression formula is not zero-indexed.  Notice I am starting at position 1, not 0 as you would using a strVar.Substring() method.  Perhaps this is a bug or did they just do that to keep us confused and on our toes? 

    =) 

    Thanks!

    Sunday, December 11, 2005 9:39 PM
  •  J.H. Chrysler wrote:
    Here's another dandy one I have been struggling with.
    All my date fields are stored as numeric values as YYYYMMDD.


    I struggle with understanding why people do this. It makes no sense and only leads to problems.

    My suggestion would be to fix it.

    This is one of my major pet peeves.
    Monday, December 12, 2005 9:35 AM
  • I agree, but unfortunately I have no control over the data structure in this instance.  It's an old COBOL database based on Vision flat files.  I am simply trying to write a web interface for it.  If I could fix it, I would....and will probably be corrected eventually but ya....Nothing but a headache.
    Monday, December 12, 2005 4:29 PM
  •  Blair Allen Stark wrote:

    I struggle with understanding why people do this. It makes no sense and only leads to problems.


    YYYYMMDD or YYYYMMDD_HHMiSS format is often used in filenames.

    Monday, January 29, 2007 5:59 PM