RE: Help with time stamps in SQL

That's what I need...
Thanks a lot!
 
Valerio

casey_r@xxxxxxxxxxxxxxxx 2/2/2007 11:26 AM >>>

Valerio,

You can use CURDATE() and CURTIME() to get the current date and time.
However, those functions return a timestamp value. Here is a sample UPDATE
statement that converts those values into the numeric values you need.

UPDATE mylib/myfile
SET datefield = DECIMAL('1'||SUBSTR(CHAR(CURDATE(),ISO),3,2)||
                        SUBSTR(CHAR(CURDATE(),ISO),6,2)||
                        SUBSTR(CHAR(CURDATE(),ISO),9,2),7,0),
    timefield = DECIMAL(SUBSTR(CHAR(CURTIME()),1,2)||
                        SUBSTR(CHAR(CURTIME()),4,2)||
                        SUBSTR(CHAR(CURTIME()),7,2),6,0)
WHERE <conditions go here>

CHAR(CURDATE(),ISO) returns the current date in "yyyy-mm-dd" format.
CHAR(CURTIME()) returns the current time in "hh:mm:ss" format.

If you need dates prior to 01-01-2000, the code could be updated to handle
the zero century digit.

Have fun!
Richard




-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Valerio Vincenti
Sent: Friday, February 02, 2007 10:46 AM
To: midrange-l@xxxxxxxxxxxx 
Subject: Help with time stamps in SQL


I have a number of mass database updates where SQL would be very convenient
instead of writing RPG programs.

The complication is that my UPDATE statement also needs to populate a couple
of audit fields: a 7 digit numeric field that holds the Last Change Date
(CYYMMDD format) and a 6 digit numeric field for the Last Change Time
(HHMMSS format).

For example, a record updated on Jan 20, 2007 at 11:30:43AM should be
updated with Change Date = 1070120 and Change Time = 113043 (since we are
already in the third millennium, I don't need to be concerned with the zero
century digit for dates earlier than 2000).

Is there a way to retrieve the current date and time into my UPDATE
statement and format them to obtain the time stamp fields that I need?

Thank you!

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.17.19/663 - Release Date: 2/1/2007
2:28 PM