473,320 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
4 9045
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: John Norvell | last post by:
I just noticed that recently the javascript I was using to format and display the last modified date of my web page is always reporting the current date instead. It used to work fine. This is true...
3
by: Kevin Gibbons | last post by:
Hello all, I've browsed through past usenet archives, but can't seem to come across quite the javascript I'm looking for. I'm looking for a simple javascript that will display the date as such:...
7
by: James P. | last post by:
Hello there, In my asp.net page using VB, I have a date text field in mm/dd/yyyy format. When a date is entered, I'd like to validate it to make sure the date is greater than or equal to the...
6
by: vijayk | last post by:
Hi all, I have a field which has data as YYYYMMDD, and I have to find the age of the person by substracting it from current date. can you please please advice... thanks
14
by: YiMkiE | last post by:
I am a newbie of C and I need to do a program to get the current date information only, without the time. I have my code here: #include <stdio.h> #include <time.h> #include <string.h> int...
0
by: Shawger Lager | last post by:
Hi, I am new to DB2 (and SQL) and I am having some problems with the current date. I am trying to make sure the date in the table review is not past the current day using a constriant. I have tried...
3
by: seanw89 | last post by:
I have a bit trouble in converting CURRENT DATE into a integer type as YYYYMMDD. The following script is trying to find a day matching a date column with integer type with yesterday (CURRENT DATE...
5
by: bruce24444 | last post by:
I have a database which assigns warranty claims to people with a main screen showing number of files assigned to each person. The number assigned shows day, week, month and year numbers so they can...
2
by: NitinSawant | last post by:
Hello Experts, I'm newbie to php, I want to get date 30 day back from current date using php. i'm able to get current date, $currentDate= date("d-M-y",time());
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.