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,
>

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,
> >>
> >

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,
>>> >>
>>> >
>>
>