Decoding string to DateTime

Question

I have DateTime in database as string for example: 6@11@2012@1@37 I have the decoding method like this:

public static DateTime DecodeTime(string time)
        {
            int day = Int32.Parse(time.Split('@')[0].Trim());
            int month = Int32.Parse(time.Split('@')[1].Trim());
            int year = Int32.Parse(time.Split('@')[2].Trim());
            int hour = Int32.Parse(time.Split('@')[3].Trim());
            int minute = Int32.Parse(time.Split('@')[4].Trim());

            DateTime decode = new DateTime(year, month, day, hour, minute, 0);

            return decode;
        }

What i am trying to do is trying to put information in dataset like this:

 public DataSet GetAllBooking()
        {
            string connString = ConfigurationManager.ConnectionStrings["SQL2012_892524_amritConnectionString"].ConnectionString;
            DataSet dataSet = new DataSet();
            // Create connection object
            //OleDbConnection oleConn = new OleDbConnection(connString);
            SqlConnection oleConn = new SqlConnection(connString);
             try
            {
                oleConn.Open();
                string sql = "SELECT TOP (50) Driver.FirstName + Driver.LastName AS DriverName, Booking.BookingId, Driver.CarId AS CarUsed, Booking.CreateDateTime AS BookingDateTime, Booking.Status FROM Booking INNER JOIN Customer ON Booking.CustomerId = Customer.CustomerId INNER JOIN Driver ON Booking.DriverId = Driver.DriverId ORDER BY Booking.BookingId DESC";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, oleConn);
                dataAdapter.Fill(dataSet, "Booking");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                oleConn.Close();
            }
            if (dataSet.Tables.Count <= 0)
                return null;
            else
                return dataSet;
        }

The column in the database which saves date and time as string is CreationTime.

Is it possible to encode those time with in the sql by calling the DecodeTime methos and display it to gridview.

Answer 1

This can be achieved using String.Format Method.

Bind the GridView to your data source as usual and declare a Template Column in the markup as follows.

<asp:TemplateField HeaderText="Date">
    <ItemTemplate>
        <%# String.Format("{2}/{1}/{0} {3}:{4}:00", 
                  Eval("ColName").Split('@') ) %>
    </ItemTemplate>
<asp:TemplateField>

Answer 2

SqlDataAdapter/DataTable/DataColumn does not provide any mechanism for intercepting the values and changing their types to DateTime (you could only convert something to a custom class). Your best bet is to do the conversion in the SQL query (although the string functions are rather limited so the query will be very long).

Another option is to add a new DateTime column to the table before returning and traverse through every row to call your method.

if (dataSet.Tables.Count == 0)
    return null;

dataSet.Tables[0].Columns.Add(new DataColumn("CreationTime2", typeof(DateTime)));
foreach (var r in dataSet.Tables[0].Rows)
    r["CreationTime2"] = DecodeTime(r["CreationTime"]);

return dataSet;