none
DB2 Timestamp returns DateTime in C# RRS feed

  • Question

  • Hi.

    My UI app is using C# and calling DB2 with DB2Connect to displays result of query

    SELECT * FROM myTable.

    Within result there are columns with DB2 data types DATE and Timestamp.

    In the result, these columns returned as DateTime (ex. 03/29/2019 12:00 am). How to correctly Select these columns so the data in it would be as it stored in DB2 table (Date and Timestamp) in order to correctly display it on Web page? Is there any option to be specified for Date/Time data types to be handled within call?

    Thank you for help.

    Sam.



    • Edited by Sam Sam L Monday, June 24, 2019 7:21 PM
    Monday, June 24, 2019 3:12 PM

All replies

  • Hi Sam,

    To convert DateTime to Timestamp, you can refer to the following code.

        private void btConvert_Click(object sender, EventArgs e)
        {
            DateTime dateTime = new DateTime(2019, 06, 25, 13, 10, 24);
            Console.WriteLine(ConvertTimestamp(dateTime));
        }
        public static int ConvertTimestamp(DateTime time)
        {
            System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));
            return (int)(time - startTime).TotalSeconds;
        }

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 25, 2019 5:16 AM
    Moderator
  • Kyle, thank you very much for your reply. But this is not what I'm looking for.

    In query where SELECT * is used there are columns with data type Date and Timestamp. When call to DB is executed these columns returned as DateTime in format ex: 3/29/2019 12:00. Which one of this columns is Timestamp? Is there way to tell DB2 driver to convert any columns with data type of Date or Timestamp to CHAR before executing query, so result will be returned in literal format.

    When I'm using column names instead of '*' then there is a way to just put CHAR(TmStCol) and it returns timestamp string. But not in SELECT *. Is there way to do it? Any other thoughts?

    Thank you in advance.

    Sam.

    Tuesday, June 25, 2019 5:28 PM
  • Hi Sam,

    If you use "*", I am afraid that you can't convert the specified field. You can only save all the data to the DataTable and then convert the specified columns.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 26, 2019 5:35 AM
    Moderator
  • Hi Kyle,

    I do save the result into DataTable and when processing each column to determine if it is Timestamp, it is showing that DataType is DateTime. When I cinvert it within C# to string, it is not working as expected.

    If timestamp's last 6 digits are all zeroes I can't determine which column is Timestamo, but if not all zeroes, then it is working if I'm using below code:  

    DataTable tbl = new DataTable()

    DataRow dr = tbl.NewRow();

    for (int k = 0; k < spParms.ds.Tables[0].Columns.Count; k++) 

      dt = String.Format("{0:yyyy-MM-dd}", ((DateTime)(ds.Tables[0].Rows[i].ItemArray[k])).Date);

      if (((DateTime)         (ds.Tables[0].Rows[i].ItemArray[k])).TimeOfDay.ToString().Trim() == "00:00:00") {

           dr[n] = dt.Substring(0, 10); 

        else

       { 

            tmOfTs = ((DateTime)                

            (ds.Tables[0].Rows[i].ItemArray[k])).TimeOfDay.ToString().Trim().Substring(0).Replace(":","."); 

            dr[n] = dt + "-" + (tmOfTs.Length == 8 ? tmOfTs + ".000000" : tmOfTs.Length >= 15 ?        

            tmOfTs.Substring(0, 15) : (tmOfTs + "00000").Substring(0, 15)); 

        }

    }


    • Edited by Sam Sam L Monday, July 22, 2019 8:37 PM
    Monday, July 22, 2019 8:36 PM