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.

enter image description here

first thing - you should not call string.Split() 5 times but instead just once and reuse the same array — Knaģis, Nov 05 '12 at 15:03
Your data adapter will return whatever datatype the database specifies, so if you return a varchar you'll get a string. Couldn't you just do this using a stored procedure? — Liam, Nov 05 '12 at 15:04
What stored procedure? I cannot change the database, but i want to convert that string to datetime any how and display it to gridview. Any solution to this/ — Amrit Sharma, Nov 05 '12 at 15:07

Kaf · Answer 1 · 2012-11-05T15:40:26.577

2

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>

edited Nov 05 '12 at 15:40

answered Nov 05 '12 at 15:27

Kaf

33,101
7
58
78

Can you please elaborate how can i achieve your solution – Amrit Sharma Nov 05 '12 at 15:54
Open up your `aspx` page and then within `... ` tag of your `GridView control's markup`, place above piece of html. Then change the `ColName to your column name which has string date`. That is all you need to do. – Kaf Nov 05 '12 at 16:08
Ok i am able to do it, but is it possible not o display the column which has date time in string – Amrit Sharma Nov 05 '12 at 16:14
Your question is how to display it on Gridview isn't it? Now you don't need to display? – Kaf Nov 05 '12 at 16:17
Sorry i confused, when i followed you procedure, it creates another column which is fine. Now there are two columns one with string and other with split string. – Amrit Sharma Nov 05 '12 at 16:20
Open up your aspx page and comment/delete unwanted columns. That is it. If you can add you can delete too. – Kaf Nov 05 '12 at 16:23

Knaģis · Answer 2 · 2012-11-05T15:39:42.677

0

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;

edited Nov 05 '12 at 15:39

answered Nov 05 '12 at 15:08

Knaģis

20,827
7
66
80

How can i do conversion in sql query? Any help or hint ? – Amrit Sharma Nov 05 '12 at 15:11
which SQL server and version are you using? – Knaģis Nov 05 '12 at 15:15
added another example how to convert it in the .net end since the sql conversion will be quite long – Knaģis Nov 05 '12 at 15:26
gives me this error "Error 1 foreach statement cannot operate on variables of type 'System.Data.DataTable' because 'System.Data.DataTable' does not contain a public definition for 'GetEnumerator' C:\Users\smartamrit\Desktop\Admin Site\northstar\northstar\DriverJob.aspx.cs 46 13 northstar " – Amrit Sharma Nov 05 '12 at 15:34
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/19099/discussion-between-amrit-sharma-and-knais) – Amrit Sharma Nov 05 '12 at 15:47

Decoding string to DateTime

2 Answers2