none
DB2 DateTime stamp

    Question

  • Hello All - I am having a few problem with timestamps when connecting to a db2 database thru VB. Ultimaly i want to grab all records within a month time frame and count the top 5 records. Whenever i add datetime strings it returns 0 rows. whenever i remove monthstart and monthend from the code it will return datetime stamps to the textbox

    sample code below:

    Sub TopIssues()

     Dim MonthStart As String
     Dim MonthEnd As String

    'returns the 1st of the current month

    'MonthStart = Format(DateAdd(DateInterval.Month, DateDiff(DateInterval.Month, Date.MinValue, Today()), Date.MinValue), ""MM/dd/yyy 00:00:00"")
     'returns the last day of the current month
     MonthEnd = Format(DateAdd(DateInterval.Month, DateDiff(DateInterval.Month, Date.MaxValue, Today()), Date.MaxValue), "MM/dd/yyy 00:00:00")

    Try
                connectionString = "DSN=SC1101;UID=username;Pwd=password"
                TopIssues = "select open_time from probsummarym1 where open_time between'" & MonthStart & "' and '" & MonthEnd & "'"

     conn = New OdbcConnection(connectionString)
                conn.Open()
                comm = New OdbcCommand(TopIssues, conn)
                dr = comm.ExecuteReader()


                While (dr.Read())
                    

                        'adds records to the array
                    ItemList.Add(dr.GetValue(0))

                     

                    End While
                'prints records from the  array to a rich text box
                For i = 0 To ItemList.Count - 1
                    RichTextIssues.AppendText(ItemList.Item(i) & vbNewLine)

                Next
            Catch

            End Try
            conn.Close()
            dr.Close()
            comm.Dispose()
            conn.Dispose()

    end sub

    Sunday, October 10, 2010 12:52 AM

Answers

  • Thank you after 4 days of trying every format I finally got it to work

    Format has to be as follows when passing to a DB2 database (YYYY-MM-DD-hh.mm .ss.uuuuuu)

    "select open_time from probsummarym1 where open_time between '2010-10-01-01.00.00.000000' and '2010-10-30-01.00.00.000000' fetch first 5 rows only"

     

    I hope this helps someone else


    Jeremy Howard
    • Marked as answer by JDHoward Monday, October 11, 2010 8:54 PM
    Monday, October 11, 2010 8:54 PM

All replies

  • I assume that you first should investigate how a datetime in an IBM DB2 database is stored. 

    What I see on this page it is ISO and not the USA way like you use

    http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

    But my knowledge of a DB2 database is less than a beginner currently.

     


    Success
    Cor
    Sunday, October 10, 2010 5:54 AM
  • Its current stored as follows:

     

    month/day/year hour/minute/second am/pm.

     

    I pulled that earlier from the database.

     

    I tried several formats to make sure i wasn't inputting it in correcrly

     

    yyyy-mm-dd

    yyyy-dd-mm

    tried short dates as well

    10/01/10

    10/01/10 00.00.00

    at one point i tried concatenating the date and time formats together

    MonthStart Date + MonthStartTime

    I'm baffled at this point and been stuck on this for the last 3 days. appreciate any help.

     

    Thanks for the reply!

     

    Sunday, October 10, 2010 6:15 AM
  • Try   ISO8601  date format that is understood by every database  ( even that of MS )

     

    regards

     

     


    http://code.msdn.microsoft.com/UserAccount/UserProfile.aspx?UserName=MPosseth ; http://www.vbdotnetcoder.com/
    Sunday, October 10, 2010 8:36 AM
  • Thank you after 4 days of trying every format I finally got it to work

    Format has to be as follows when passing to a DB2 database (YYYY-MM-DD-hh.mm .ss.uuuuuu)

    "select open_time from probsummarym1 where open_time between '2010-10-01-01.00.00.000000' and '2010-10-30-01.00.00.000000' fetch first 5 rows only"

     

    I hope this helps someone else


    Jeremy Howard
    • Marked as answer by JDHoward Monday, October 11, 2010 8:54 PM
    Monday, October 11, 2010 8:54 PM
  • Thank you after 4 days of trying every format I finally got it to work

    Format has to be as follows when passing to a DB2 database (YYYY-MM-DD-hh.mm .ss.uuuuuu)

    "select open_time from probsummarym1 where open_time between '2010-10-01-01.00.00.000000' and '2010-10-30-01.00.00.000000' fetch first 5 rows only"

     

    I hope this helps someone else


    Jeremy Howard

    So you did not read the link I gave you sunday.

     

     
    TIMESTAMP ('2002-10-20-12.00.00.000000') 

     

    If you next time ask a question put than in your message I don't watch the replies

    This has the name ISO date time by the way. (And more precise ISO 8601)


    Success
    Cor
    Monday, October 11, 2010 9:23 PM
  • I looked at that link before you posted and even after you posted it again, it was not clicking for me until i kept trying different formats today.
    Jeremy Howard
    Monday, October 11, 2010 9:33 PM