CODEWITHSUNDEEP
c#datetimesqldatareader
BH7
BH7

Reputation: 234

Reader.GetValue date value

I am looping out the reader.GetValue from a SQL Server database, all works, however I have a column of data type Date in the table. When I view the data in the table, it shows correctly the date '18-Dec-17' with NO time.

The output of the reader is of this format: '18-Dec-17 12:00:00 AM' AND all records show 12:00:00 AM the same, regardless of the date change.

Could someone let me know whats going on here and is there an option to remove or do I need to regex or replace it?

while (reader.Read())
{
    while (cnt_field <= reader.FieldCount - 1)
    {
        db_output = db_output + "" + reader.GetValue(cnt_field) + " -\t ";
        cnt_field++;
    }//WEND
    cnt_field = 0;
    db_output = db_output + "\n\r";
}//WEND

Upvotes: 3

Views: 2800

Answers (2)

Henk Holterman
Henk Holterman

Reputation: 273701

With "" + reader.GetValue(...) you are doing an implicit reader.GetValue(...).ToString().

What you then see is the default representation of a DateTime. dotNET does not have a separate Date type.

So you will have to detect DateTime values inside your loop and apply the desired formatting.

Something like 

while (cnt_field <= reader.FieldCount - 1)
{
   object value = reader.GetValue(cnt_field);
   if (value is DateTime)
     db_output += ((DateTime)value).ToShortDateString();  // apply a std/custom Date format
   else   
      db_output += value.ToString(); 

   db_output += " -\t ";
   ....
}

Upvotes: 4

Marc Gravell
Marc Gravell

Reputation: 1064014

The call to reader.GetValue(cnt_field) is returning a DateTime ("boxed" as an object) that represents the date. Now; both in the database and in a DateTime, date-time values do not have a format - they are just the raw numeric value of the date/time. When you concatenate this with a string, the default .ToString() is used, which applies your current culture and default format specifier. So: if that isn't what you intended: you'll need to tell it what you intended:

var when = (DateTime)reader.GetValue(cnt_field);
db_output = db_output + "" + when.ToString(chosenFormat) + " -\t ";

You might also want to explicitly specify a culture - CultureInfo.InvariantCulture is usually a good choice for logging etc.

You can choose standard format specifiers or custom format specifiers to build the format you want. You might want "dd-MMM-yy", so:

db_output = db_output + "" + when.ToString("dd-MMM-yy") + " -\t ";

Upvotes: 2

Related Questions