467,202 Members | 1,083 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,202 developers. It's quick & easy.

Formatted CURRENT DATE

Is there a way in DB2 I can get the current date and time formatted.

In Oracle I could do the following

select to_char(sysdate,'yyyymmdd') from dual

returns

20070718

and

select to_char(sysdate,'hh24miss') from dual

returns

134646

I am struggling to find an easy way of doing this in DB2.

--
Steve

Jul 18 '07 #1
  • viewed: 8308
Share:
4 Replies
Steve Rainbird wrote:
Is there a way in DB2 I can get the current date and time formatted.

In Oracle I could do the following

select to_char(sysdate,'yyyymmdd') from dual

returns

20070718

and

select to_char(sysdate,'hh24miss') from dual

returns

134646

I am struggling to find an easy way of doing this in DB2.
You aren't on DB2 Viper 2 yet.. tsk, tsk ;-)

VALUES INTEGER(CURRENT DATE);
db2 =VALUES INTEGER(CURRENT DATE);

1
-----------
20070718

1 record(s) selected.

db2 =VALUES (current time, integer(current time));

1 2
-------- -----------
09:29:41 92941

1 record(s) selected.

You can cast from integer to char of course.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jul 18 '07 #2
"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:5g*************@mid.individual.net...
Steve Rainbird wrote:
>Is there a way in DB2 I can get the current date and time formatted.

In Oracle I could do the following

select to_char(sysdate,'yyyymmdd') from dual

returns

20070718

and

select to_char(sysdate,'hh24miss') from dual

returns

134646

I am struggling to find an easy way of doing this in DB2.
You aren't on DB2 Viper 2 yet.. tsk, tsk ;-)

VALUES INTEGER(CURRENT DATE);
db2 =VALUES INTEGER(CURRENT DATE);

1
-----------
20070718

1 record(s) selected.

db2 =VALUES (current time, integer(current time));

1 2
-------- -----------
09:29:41 92941

1 record(s) selected.

You can cast from integer to char of course.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks Serge

--
Steve

Jul 18 '07 #3
You don't need to upgrade to Viper 2(Is that DB2 9.x ?).
DB2 V8.2 already support that.
------------------------- Commands Entered -------------------------
connect to SAMPLE user db2admin using ********;
--------------------------------------------------------------------

Database Connection Information

Database server = DB2/NT 8.2.7
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
------------------------- Commands Entered -------------------------
VALUES INTEGER(CURRENT DATE);
--------------------------------------------------------------------

1
-----------
20070719

1 record(s) selected.
------------------------- Commands Entered -------------------------
VALUES (current time, integer(current time));
--------------------------------------------------------------------

1 2
-------- -----------
22:07:53 220753

1 record(s) selected.
Jul 19 '07 #4
Tonkuma wrote:
You don't need to upgrade to Viper 2(Is that DB2 9.x ?).
DB2 V8.2 already support that.
------------------------- Commands Entered -------------------------
connect to SAMPLE user db2admin using ********;
--------------------------------------------------------------------

Database Connection Information

Database server = DB2/NT 8.2.7
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
------------------------- Commands Entered -------------------------
VALUES INTEGER(CURRENT DATE);
--------------------------------------------------------------------

1
-----------
20070719

1 record(s) selected.
------------------------- Commands Entered -------------------------
VALUES (current time, integer(current time));
--------------------------------------------------------------------

1 2
-------- -----------
22:07:53 220753

1 record(s) selected.

Hi,

Viper 2 might be 9.5, its not official yet.

I think Serge means that something similar to
to_char(sysdate,'yyyymmdd') will be available in Viper 2 but instead
gave a solution that is working on v8. He had posted a thread with
upcoming Oracle (and other) like functionality in Viper 2 that will
simplify cross vendor coding and migration.

With kind regards,
Michael
Jul 19 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by vijayk | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.