The only format that is accepted is
YYYY-DD-MM HH24:MI:SS
Wayne Stevens
Wayne.[Email address protected]
-----Original Message-----
From: Das Ashim K
[mailto:db2udbdba-ezmlmshield-x48002045.[Email address protected]
Sent: Wednesday, December 26, 2007 11:58 AM
To: LazyDBA Discussion
Subject: RE: Timestamp conversion routines
Hi,
Thanks for the Tip. It seems that it understands only one mask string in
the order you specifies. Any variation it throws up.
However the following does not work: I have transposed the MM and DD and
I get the following error:
ERROR [42815] [IBM][DB2/AIX64] SQL0171N The data type, length or value
of argument "2" of routine "SYSIBM.TIMESTAMP_FORMAT" is incorrect.
SQLSTATE=42815
ERROR [42815] [IBM][DB2/AIX64] SQL0171N The data type, length or value
of argument "2" of routine "SYSIBM.TIMESTAMP_FORMAT" is incorrect.
SQLSTATE=42815
select timestamp_format('2007-26-12 12:50:10', 'YYYY-DD-MM HH24:MI:SS')
from sysibm.sysdummy1
------------------------------------------------------------------------
----
Also the following does not work where I change the order for the YYYY
Get the same error
select timestamp_format('12-26-2007 12:50:10', 'MM-DD-YYYY HH24:MI:SS')
from sysibm.sysdummy1
Thanks,
Ashim.
-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x26260660.[Email address protected]
Sent: Wednesday, December 26, 2007 12:45 PM
To: LazyDBA Discussion
Subject: RE: Timestamp conversion routines
Here is another example.
select timestamp_format('2007-12-26 12:50:10', 'YYYY-MM-DD HH24:MI:SS')
from sysibm.sysdummy1
-----Original Message-----
From: Edwards, Ed
Sent: Wednesday, December 26, 2007 12:34 PM
To: LazyDBA Discussion
Subject: RE: Timestamp conversion routines
Importance: High
OOPS! Correction! Hehe.
select timestamp('03/04/2005','00.00.00') from sysibm.sysdummy1
-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x73137082.[Email address protected]
Sent: Wednesday, December 26, 2007 12:31 PM
To: LazyDBA Discussion
Subject: RE: Timestamp conversion routines
The code below may help.
timestamp(date('03/04/2005','00.00.00'))
Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Dr. Eric J. Smith is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: Das Ashim K
[mailto:db2udbdba-ezmlmshield-x93255900.[Email address protected]
Sent: Wednesday, December 26, 2007 12:23 PM
To: LazyDBA Discussion
Subject: Timestamp conversion routines
HI,
What is the equivalent of Oracle's to_date function in db2 ?
Eg1:
insert into table_a ( start_dttm ) values( to_date( '01-dec-2007
10:30:00', 'dd-mon-yyyy hh:mi:ss') );
Eg2:
insert into table_a ( start_dttm ) values( to_date( '01-12-2007
08:30:00', 'dd-mm-yyyy hh:mi:ss') );
where start_dttm is a date column.
PL note that the date_mask changed in the second example.
How do I do the same thing in db2.
In other words I want to insert into a timestamp column from a varchar
string that represents the date and time. I want to specify a date_mask
that will match the varchar string.
Please provide a couple of examples.
Thanks for your help.
Ashim
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page