DATE TIME Values


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: DATE TIME Values

  1. #1
    Frost Guest

    DATE TIME Values


    Hi everyone, I hope someone can help me with my question. I have an application
    that is written in Visual Basic and SQL Server. Recently we have been trying
    to convert this App to DB2 UDB. The problem I am facing right now involves
    the DATETIME Value.

    Here is my SQL Statement:
    INSERT INTO EVENTTABLE VALUES ('7/26/2001 10:15:48 AM','300')

    Here are my Fields:
    fDateTimeStamp(TimeStamp...Does not matter get same Error)
    fEvent(varChar)

    Here is the Error:
    [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation
    of a datetime value is incorrect. SQLSTATE=22007



    SQL Server works great. No Problems, DB2 does not like this?

    Thanks in Advance,


  2. #2
    mark Guest

    Re: DATE TIME Values


    Try using dashes. We use this format - 2001-07-26 19:00:00.0

    "Frost" <stevef@pedco.com> wrote:
    >
    >Hi everyone, I hope someone can help me with my question. I have an application
    >that is written in Visual Basic and SQL Server. Recently we have been trying
    >to convert this App to DB2 UDB. The problem I am facing right now involves
    >the DATETIME Value.
    >
    >Here is my SQL Statement:
    >INSERT INTO EVENTTABLE VALUES ('7/26/2001 10:15:48 AM','300')
    >
    >Here are my Fields:
    > fDateTimeStamp(TimeStamp...Does not matter get same Error)
    > fEvent(varChar)
    >
    >Here is the Error:
    > [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation
    >of a datetime value is incorrect. SQLSTATE=22007
    >
    >
    >
    >SQL Server works great. No Problems, DB2 does not like this?
    >
    >Thanks in Advance,
    >



  3. #3
    Frost Guest

    Re: DATE TIME Values


    I am trying to make this program to be able to use either SQL or DB2.

    Does DB2 have a Now() Function

    If not how do I make one? I know about the 'Current Timestamp' Parameter
    but DB2 is the only database that uses that. Can you make function in Db2?


    "mark " <mnuttall@nospam.com> wrote:
    >
    >Try using dashes. We use this format - 2001-07-26 19:00:00.0
    >
    >"Frost" <stevef@pedco.com> wrote:
    >>
    >>Hi everyone, I hope someone can help me with my question. I have an application
    >>that is written in Visual Basic and SQL Server. Recently we have been

    trying
    >>to convert this App to DB2 UDB. The problem I am facing right now involves
    >>the DATETIME Value.
    >>
    >>Here is my SQL Statement:
    >>INSERT INTO EVENTTABLE VALUES ('7/26/2001 10:15:48 AM','300')
    >>
    >>Here are my Fields:
    >> fDateTimeStamp(TimeStamp...Does not matter get same Error)
    >> fEvent(varChar)
    >>
    >>Here is the Error:
    >> [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation
    >>of a datetime value is incorrect. SQLSTATE=22007
    >>
    >>
    >>
    >>SQL Server works great. No Problems, DB2 does not like this?
    >>
    >>Thanks in Advance,
    >>

    >



  4. #4
    Greg Nash Guest

    Re: DATE TIME Values

    Hi,

    Try...

    CREATE FUNCTION NOW()
    RETURNS TIMESTAMP
    RETURN CURRENT TIMESTAMP

    Note there may be a difference in the resolution of timestamps between DB2 and SQL Svr.

    I know MS Access has had problems with DB2 where a table has a timestamp in the primary
    key, as Jet was rounding off 3 digits of the timestamp - then the keys didn't match the
    table. There are ways around that problem if you come across it, but I don't know the
    implications for SQL Server

    --Greg

    Frost wrote:

    > I am trying to make this program to be able to use either SQL or DB2.
    >
    > Does DB2 have a Now() Function
    >
    > If not how do I make one? I know about the 'Current Timestamp' Parameter
    > but DB2 is the only database that uses that. Can you make function in Db2?
    >
    > "mark " <mnuttall@nospam.com> wrote:
    > >
    > >Try using dashes. We use this format - 2001-07-26 19:00:00.0
    > >
    > >"Frost" <stevef@pedco.com> wrote:
    > >>
    > >>Hi everyone, I hope someone can help me with my question. I have an application
    > >>that is written in Visual Basic and SQL Server. Recently we have been

    > trying
    > >>to convert this App to DB2 UDB. The problem I am facing right now involves
    > >>the DATETIME Value.
    > >>
    > >>Here is my SQL Statement:
    > >>INSERT INTO EVENTTABLE VALUES ('7/26/2001 10:15:48 AM','300')
    > >>
    > >>Here are my Fields:
    > >> fDateTimeStamp(TimeStamp...Does not matter get same Error)
    > >> fEvent(varChar)
    > >>
    > >>Here is the Error:
    > >> [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation
    > >>of a datetime value is incorrect. SQLSTATE=22007
    > >>
    > >>
    > >>
    > >>SQL Server works great. No Problems, DB2 does not like this?
    > >>
    > >>Thanks in Advance,
    > >>

    > >



  5. #5
    Michael Kittel Guest

    Re: DATE TIME Values


    We used conditional compilation in VB to handle the differences between SQL
    and DB2. There are two specific differences:

    1. SQL time values have a resolution of 1 millisecond where DB2 time values
    have a resolution of 1 microsecond.
    2. To get the current date/time from the server, SQL uses "getDate()" where
    DB2 uses "CURRENT TIMESTAMP".

    Here is an example of the conditional compilation we used to get the server
    date/time from either SQL or DB2:

    #If DB2Version Then
    'NOTE: DB2 requires table name in the select statement
    ' and uses "current timestamp" for the server date/time
    strSQL = "SELECT current timestamp AS CurrentDate FROM SEQUENCE_VALUE;"
    #Else
    'NOTE: SQL Server uses getDate for the server date/time
    strSQL = "SELECT getDate() AS CurrentDate"
    #End If

    Hope this helps.


    Greg Nash <gnash@namoicotton.com.au> wrote:
    >Hi,
    >
    >Try...
    >
    >CREATE FUNCTION NOW()
    > RETURNS TIMESTAMP
    > RETURN CURRENT TIMESTAMP
    >
    >Note there may be a difference in the resolution of timestamps between DB2

    and SQL Svr.
    >
    >I know MS Access has had problems with DB2 where a table has a timestamp

    in the primary
    >key, as Jet was rounding off 3 digits of the timestamp - then the keys didn't

    match
    >the
    >table. There are ways around that problem if you come across it, but I

    don't know the
    >implications for SQL Server
    >
    >--Greg
    >
    >Frost wrote:
    >
    >> I am trying to make this program to be able to use either SQL or DB2.
    >>
    >> Does DB2 have a Now() Function
    >>
    >> If not how do I make one? I know about the 'Current Timestamp' Parameter
    >> but DB2 is the only database that uses that. Can you make function in

    Db2?
    >>
    >> "mark " <mnuttall@nospam.com> wrote:
    >> >
    >> >Try using dashes. We use this format - 2001-07-26 19:00:00.0
    >> >
    >> >"Frost" <stevef@pedco.com> wrote:
    >> >>
    >> >>Hi everyone, I hope someone can help me with my question. I have an

    application
    >> >>that is written in Visual Basic and SQL Server. Recently we have been

    >> trying
    >> >>to convert this App to DB2 UDB. The problem I am facing right now involves
    >> >>the DATETIME Value.
    >> >>
    >> >>Here is my SQL Statement:
    >> >>INSERT INTO EVENTTABLE VALUES ('7/26/2001 10:15:48 AM','300')
    >> >>
    >> >>Here are my Fields:
    >> >> fDateTimeStamp(TimeStamp...Does not matter get same Error)
    >> >> fEvent(varChar)
    >> >>
    >> >>Here is the Error:
    >> >> [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation
    >> >>of a datetime value is incorrect. SQLSTATE=22007
    >> >>
    >> >>
    >> >>
    >> >>SQL Server works great. No Problems, DB2 does not like this?
    >> >>
    >> >>Thanks in Advance,
    >> >>
    >> >

    >



  6. #6
    Russell Collins Guest

    Re: DATE TIME Values


    I know this is late but you could also make a trigger in DB2 that will update
    the time field anytime there is an insert or update. This will save you
    time and effort when it comes to coding your program. If you would like
    me to elaborate, contact me.

    --russell

    "Michael Kittel" <mkittel@ddig.com> wrote:
    >
    >We used conditional compilation in VB to handle the differences between

    SQL
    >and DB2. There are two specific differences:
    >
    >1. SQL time values have a resolution of 1 millisecond where DB2 time values
    >have a resolution of 1 microsecond.
    >2. To get the current date/time from the server, SQL uses "getDate()" where
    >DB2 uses "CURRENT TIMESTAMP".
    >
    >Here is an example of the conditional compilation we used to get the server
    >date/time from either SQL or DB2:
    >
    >#If DB2Version Then
    > 'NOTE: DB2 requires table name in the select statement
    > ' and uses "current timestamp" for the server date/time
    > strSQL = "SELECT current timestamp AS CurrentDate FROM SEQUENCE_VALUE;"
    >#Else
    > 'NOTE: SQL Server uses getDate for the server date/time
    > strSQL = "SELECT getDate() AS CurrentDate"
    >#End If
    >
    >Hope this helps.
    >
    >
    >Greg Nash <gnash@namoicotton.com.au> wrote:
    >>Hi,
    >>
    >>Try...
    >>
    >>CREATE FUNCTION NOW()
    >> RETURNS TIMESTAMP
    >> RETURN CURRENT TIMESTAMP
    >>
    >>Note there may be a difference in the resolution of timestamps between

    DB2
    >and SQL Svr.
    >>
    >>I know MS Access has had problems with DB2 where a table has a timestamp

    >in the primary
    >>key, as Jet was rounding off 3 digits of the timestamp - then the keys

    didn't
    >match
    >>the
    >>table. There are ways around that problem if you come across it, but I

    >don't know the
    >>implications for SQL Server
    >>
    >>--Greg
    >>
    >>Frost wrote:
    >>
    >>> I am trying to make this program to be able to use either SQL or DB2.
    >>>
    >>> Does DB2 have a Now() Function
    >>>
    >>> If not how do I make one? I know about the 'Current Timestamp' Parameter
    >>> but DB2 is the only database that uses that. Can you make function in

    >Db2?
    >>>
    >>> "mark " <mnuttall@nospam.com> wrote:
    >>> >
    >>> >Try using dashes. We use this format - 2001-07-26 19:00:00.0
    >>> >
    >>> >"Frost" <stevef@pedco.com> wrote:
    >>> >>
    >>> >>Hi everyone, I hope someone can help me with my question. I have an

    >application
    >>> >>that is written in Visual Basic and SQL Server. Recently we have been
    >>> trying
    >>> >>to convert this App to DB2 UDB. The problem I am facing right now

    involves
    >>> >>the DATETIME Value.
    >>> >>
    >>> >>Here is my SQL Statement:
    >>> >>INSERT INTO EVENTTABLE VALUES ('7/26/2001 10:15:48 AM','300')
    >>> >>
    >>> >>Here are my Fields:
    >>> >> fDateTimeStamp(TimeStamp...Does not matter get same Error)
    >>> >> fEvent(varChar)
    >>> >>
    >>> >>Here is the Error:
    >>> >> [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string

    representation
    >>> >>of a datetime value is incorrect. SQLSTATE=22007
    >>> >>
    >>> >>
    >>> >>
    >>> >>SQL Server works great. No Problems, DB2 does not like this?
    >>> >>
    >>> >>Thanks in Advance,
    >>> >>
    >>> >

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center