none
Convert TimeSpan to Time (no Date) for insert to Access table RRS feed

  • Question

  • I have a DataTable which is generated dynamically from a query to a DB2 database using the OdbcDataAdapter.Fill( datatable ) method. One of the columns in the source query is the time-only portion of a timestamp, calculated using the DB2 function TIME( ), which is self-explanatory. This is recognized by the Odbc managed provider as an OdbcType.Time field, but the underlying .NET type used to store the data in the datatable is a TimeSpan.

    Next I push this datatable into an MS Access database using an OleDbDataAdapter. The destination field for the time column is a DATETIME Jet data type, which can handle both date-only and time-only datetime values. The problem is that the CLR and/or OleDb managed provider doesn't know how to convert the TimeSpan object back to a Time (specifically an OleDb.DBTime type).

    Obviously there is no Time datatype in .NET. TimeSpan holds only a length of time, not the "location" in history, so it makes sense that you can't automatically convert a TimeSpan to a DateTime.  "2 hours and 15 minutes" (TimeSpan) does not imply 2:15 AM. In fact conversion to a TimeSpan seems like an mistake in the Odbc provider implementation, because a Time or TimeStamp data type does imply an exact point in history. Coverting to a TimeSpan destroys that information.

    And on the other hand, the .NET DateTime object requires a date portion. All parameterized constructors for DateTime require a year, month, and day. If you instantiate a DateTime with no arguments it defaults to 1/1/0001 at 12:01 AM. Using DateTime.Parse("5:00 AM"), for example, automatically appends the current date to that time. Pushing this to the Jet table through the OleDbDataAdapter will include the date along with the time, even though I don't want the date part.

    In order to push only a time value to a Jet database, am I forced to abandon a DataAdapter insert command with strongly-typed parameters and resort to manually constructing a query string, using something like the following for the time-only literal?

    "#" + [time span object].ToString("hh\:mm\:ss") + "#"
    
    


    I guess fundementally I'm dealing with the fact that .NET does not have a Time data type, and further more that the DateTime data type is not have the precision of most database engines' timestamp type. So when using .NET to read and write either precise (TimeStamp) or time-only (TimeStamp or Time) information to and from databases, it seems I am at the mercy of the managed provider implementation of how to translate (or lose in translation) these data elements.

    Bottom line: How do I preserve either a time-only (OdbcType.Time / OleDbType.DBTime) or precise date time (OdbcType.TimeStamp / OleDbType.DBTimeStamp) data element while using .NET to manipulate data?


    jmh
    • Edited by Joshua Honig Thursday, September 22, 2011 1:55 PM
    Thursday, September 22, 2011 1:47 PM

All replies

  • Could you possibly store the Time value as a long and when retrieving the value use a TimeSpan structure to get hours, minutes and seconds back as the constructor of a TimeSpan accepts ticks which would be the long stored in MS-Access. Granted this is not a great option especially if you want to display the data in a .NET application w/o the need to format it properly but it is an option. Also I have not tried this as I am home (we use DB2 at work) but perhaps the time value could be stored as a string as per DB2 Data Type and Code Page Mappings 
    KSG
    • Proposed as answer by Larcolais Gong Wednesday, September 28, 2011 2:15 PM
    Friday, September 23, 2011 12:22 PM
  • Basically you are at the mercy of the data types supported by the database. Access doesn't really have a timestamp or timespan data type; the Date data type is stored internally as a double precision value which includes both the date and time.

    As Kevin mentioned you can store the .NET TimeSpan in an Access database but not in its native structure. Ticks is a 64-bit Integer representation and Access does not have an equivalent data type. That leaves storing the individual parts (days, hours, minutes, seconds, milliseconds) as corresponding Integer data types or storing all the parts in a string and parsing it out after it has been read from the database.

    Just an FYI, for an Access database the recommended method for database access when using .NET is the ACE OLEDB Provider (or Jet OLEDB for legacy code).


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 23, 2011 8:32 PM
  • Quick clarification -- I use Odbc to connect to the DB2 database and then OleDb ( ACE.12.0 provider ) for connecting to the Jet database.


    jmh
    Saturday, September 24, 2011 1:37 AM
  • Hello,

    Would you mind letting us know how it goes now?

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 28, 2011 2:16 PM
  • Quick clarification -- I use Odbc to connect to the DB2 database and then OleDb ( ACE.12.0 provider ) for connecting to the Jet database.


    jmh


    On a side note have you ever considered using the IBM-DB2 native provider? Here is an example/mock up using VS2008 or higher

    Private Sub TempCode()
        Using cn As New iDB2Connection("datasource=MyDataSource;DefaultCollection=Kevin;")
            Dim dt As New DataTable With {.TableName = "Demo"}
            Dim cmd As New iDB2Command
    
            Dim Statement As String = _
            <SQL>
                SELECT Identifier, MyTimeColumn FROM SomeTable
            </SQL>.Value
    
            cmd.Connection = cn
            cn.Open()
            cmd.CommandText = Statement
    
            Dim reader As iDB2DataReader
            reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
            If (reader.Read()) Then
                Dim MyTime = reader.GetiDB2Time(1)
                ' Do something with value
            End If
        End Using
    End Sub
    

     

     


    KSG
    Wednesday, September 28, 2011 3:15 PM
  • @Larcolais, @Kevininstructor:

    I appreciate the input. I haven't marked anything as an answer because unfortunately the answer really seems to be: There isn't a way to reliably preserve strongly-typed time information when using the ADO.NET managed providers to connect to mixed data sources. I do have access to the DB2 native provider, but no matter what provider you use to get the info from DB2 it still must be stored as .Net type in the datatable in memory. The generic Odbc provider already recognizes Time-only and TimeSpan datatypes on download from DB2.

    So the answer seems to be as I described in my original post:

    In order to push only a time value to a Jet database, am I forced to abandon a DataAdapter insert command with strongly-typed parameters and resort to manually constructing a query string, using something like the following for the time-only literal?

    "#" + [time span object].ToString("hh\:mm\:ss") + "#"
    

    The answer is yes, I am forced to manually convert data types to strings.

    @Kevin - I still gave you helpful votes for your responses.


    jmh
    Saturday, October 1, 2011 1:48 PM
  •         Dim Statement As String = _
            <SQL>
                SELECT Identifier, MyTimeColumn FROM SomeTable
            </SQL>.Value

    Nice trick using in-line XML to construct a literal SQL statement! Alas I generally use C#, which does not support XML literals. C# however supports multi-line "verbatim string literals" using the @"..." syntax.


    jmh

    Saturday, October 1, 2011 1:52 PM
  •         Dim Statement As String = _
            <SQL>
                SELECT Identifier, MyTimeColumn FROM SomeTable
            </SQL>.Value

    Nice trick using in-line XML to construct a literal SQL statement! Alas I generally use C#, which does not support XML literals. C# however supports multi-line "verbatim string literals" using the @"..." syntax.


    jmh


    I usually will create an SQL statement say in IBM-DB2 SQL editor then decide to a) create a parameterized command b) stick with the literal SQL statement or place the statement into a text file which then is read into the command object. The cool thing about placing SQL into a text file is when the extension is .SQL which provides syntax highlights this making the statement easier to read for others.

    Any ways the next level for the literal style is as follows where I embed variable data and use literals i.e. HTML escapes to apease the  Visual Studio as it does errors out on lesser and greater than symbols.

        Private Sub DemoIt()
            Dim Table1 As String = "INVOICE_LINE_ITEM"
            Dim Table2 As String = "INVOICE_WORK"
    
            Dim MySelectStatement = _
            <SQL>
            SELECT COLUMN_NAME, DATA_TYPE, LENGTH, COLUMN_TEXT
            FROM qsys.syscolumns 
            WHERE TABLE_SCHEMA = 'HAZ' AND   (TABLE_NAME = '<%= Table1 %>'  OR TABLE_NAME = '<%= Table2 %>' )  AND 
                (RIGHT(COLUMN_NAME,3) &lt;&gt;'_SW' AND 
                RIGHT(COLUMN_NAME,3) &lt;&gt; '_ID' AND 
                RIGHT(COLUMN_NAME,11) &lt;&gt; '_IDENTIFIER')
            UNION (SELECT 'UNKNOWN' As COLUMN_NAME, 
                          'VARCHAR' As DATA_TYPE, 
                                  7 As LENGTH, 
                          'DEFAULT' As COLUMN_TEXT 
           FROM qsys2.syscolumns FETCH first 1 row ONLY) 
           ORDER BY 
    	       COLUMN_NAME
            </SQL>.Value
    
            Console.WriteLine(MySelectStatement)
    
        End Sub
    


    Results

    SELECT COLUMN_NAME, DATA_TYPE, LENGTH, COLUMN_TEXT
    FROM qsys.syscolumns 
    WHERE TABLE_SCHEMA = 'HAZ' AND   (TABLE_NAME = 'INVOICE_LINE_ITEM'  OR TABLE_NAME = 'INVOICE_WORK' )  AND 
          (RIGHT(COLUMN_NAME,3) <>'_SW' AND 
           RIGHT(COLUMN_NAME,3) <> '_ID' AND 
           RIGHT(COLUMN_NAME,11) <> '_IDENTIFIER')
    UNION (SELECT 'UNKNOWN' As COLUMN_NAME, 
                  'VARCHAR' As DATA_TYPE, 
                  7 As LENGTH, 
                  'DEFAULT' As COLUMN_TEXT 
           FROM qsys2.syscolumns FETCH first 1 row ONLY) 
    ORDER BY 
    	COLUMN_NAME
            
    
    

     


    KSG
    Saturday, October 1, 2011 3:20 PM
  • Correct, there is no equivalent to this data type in Microsoft Access. As was mentioned you can either use a single string or multiple numeric values to represent the TimeSpan.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, October 3, 2011 2:32 PM