String representation of a datetime value in DB2

[sxr2179]

Hi,

I am trying to update a DB2 column of the DateTime datatype.

This is the query I run:

update Table1 set TimeStamp = '3/9/2009 10:44:52 AM' where Fiel_Id =
blah

This is the existing format of the DB2 table column (TimeStamp). But
I get an error saying :

<b>[IBM][CLI Driver][DB2] SQL0180N The syntax of the string
representation of a datetime value is incorrect. SQLSTATE=22007 </b>

I tried using 'MM/dd/yyyy hh:mm:ss tt' format and several other
variations just to see if it works, but nothing helps.

Can someone please help me out? Thanks in advance

[Tonkuma]

You can find datetime formats in Info Center.
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp

Please search "Datetime values".

Table 165. Formats for String Representations of Dates
Format Name, Abbreviation, Date Format, Example
International Standards Organization, ISO, yyyy-mm-dd, 1991-10-27
IBM(R) USA standard, USA, mm/dd/yyyy, 10/27/1991
IBM European standard, EUR, dd.mm.yyyy, 27.10.1991
Japanese Industrial Standard Christian Era, JIS, yyyy-mm-dd,
1991-10-27
Site-defined, LOC, Depends on the territory code of the application,
--

Table 166. Formats for String Representations of Times
Format Name, Abbreviation, Time Format, Example
International Standards Organization, ISO, hh.mm.ss, 13.30.05
IBM USA standard, USA, hh:mm AM or PM, 1:30 PM
IBM European standard, EUR, hh.mm.ss, 13.30.05
Japanese Industrial Standard Christian Era, JIS, hh:mm:ss, 13:30:05
Site-defined, LOC, Depends on the territory code of the application,
--

Timestamp strings
A string representation of a timestamp is a string that starts with a
digit and has a length of at least 16 characters. The complete string
representation of a timestamp has the form yyyy-mm-dd-hh.mm.ss.nnnnnn.
Trailing blanks may be included. Leading zeros may be omitted from the
month, day, and hour part of the timestamp, and microseconds may be
truncated or entirely omitted. If any trailing zero digits are omitted
in the microseconds portion, an implicit specification of 0 is assumed
for the missing digits. Thus, 1991-3-2-8.30.00 is equivalent to
1991-03-02-08.30.00.000000.

SQL statements also support the ODBC string representation of a
timestamp, but as an input value only. The ODBC string representation
of a timestamp has the form yyyy-mm-dd hh:mm:ss.nnnnnn.

[Tonkuma]

You can specify USA format for timestamp column by using TIMESPAMP
buitin function, like this.

------------------------- Commands Entered -------------------------
VALUES TIMESTAMP('3/9/2009', '10:44 AM');
--------------------------------------------------------------------

1
--------------------------
2009-03-09-10.44.00.000000

1 record(s) selected.

Note that you can't specify seconds by USA format.
------------------------- Commands Entered -------------------------
VALUES TIMESTAMP('3/9/2009', '10:44:52 AM');
--------------------------------------------------------------------

1
--------------------------

[sxr2179]

Thank you very much for your replies. I found that the DB2 query
which works on my table is of the format:

UPDATE Table1 SET LastUpdatedTime= '2009-03-08 09:47:00' where
FieldId = Blah

So after playing around a little bit, this is what worked for me:

string sql = "update Table1 SET LastUpdatedTime= '" + dt.ToString
("yyyy-MM-dd hh:mm:ss") +"' where FieldId= " + blah + ";";

where, DateTime dt = DateTime.Now;

PS: Case matters.

[Tonkuma]

On Mar 11, 2:50 am, sxr2179 <sameera.ras...@gmail.com> wrote:
>
> UPDATE Table1 SET LastUpdatedTime= '2009-03-08 09:47:00'  where
> FieldId = Blah
>

That is the ODBC string representation of a timestamp. The ODBC string

representation of a timestamp has the form yyyy-mm-dd hh:mm:ss.nnnnnn

(microseconds may be truncated or entirely omitted).