Bob,
The first thing you should note is that the date values are not "saved as
MM/DD/YYYY" (or any other character representation !!! Instead they are
stored in an internal date format. It is the client settings which
control how a date is displayed in output and, more importantly, how
strings which represent dates are handled. This applies inside
application objects such as stored procedures and triggers as well as in
SQL submitted from the command line.
Take the following SP for example -
CREATE PROCEDURE SP001DATE
(OUT p_later CHAR(1))
SPECIFIC SP001DATE
BEGIN
SET p_later = 'N';
IF (CURRENT DATE '11/06/2006') THEN
SET p_later = 'Y';
END IF;
END#
If I create this SP, and run it from my UK client I get the result 'Y',
since it is later than 11th June 2006. However if I connect to the same
database from a US client and run the same procedure then I get the result
'N', since 6th November 2006 is still in the future at this point.
Here I chose a date that was in the first 12 days of the month, so that the
procedure would actually execute correctly. If I had set the date to
'13/06/2006' then from my UK client it would have run correctly, but from a
US client I'd have got -
SQL0181N The string representation of a datetime value is out of range.
SQLSTATE=22007
Note that this is a runtime error. I can equally well build from my UK
client a procedure with the string set to '06/13/2006' but it fails when I
run it.
The moral of this story is that you should never be using character
representations of dates directly in routine code without taking steps to
ensure that they are validated and interpreted uniformly no matter what the
client locale settings are.
Using the ISO setting of CHAR is one way of achieving this, at least for
data being sent out. At least then you are guaranteed a standard format
no matter where you run it from.
And in the SP above you'd want to construct a value of type DATE from the
string in a standard way, rather than using the literal. I've got some
samples of this which I could dig out if you need.
More difficult is when a client wants to send a literal string (as a CHAR())
which they actually want treated as a date within the SP. I think a good
approach is to insist that they actually use a DATE as the parameter
wherever possible : then validation is done automatically. One area where
you have issues with this is in handling XML documents as input : and why
you really should be validating XML against XSDs.
HTH
Phil
sy*****@gmail.com wrote:
We commonly use triggers to log changes to our main tables to
historical log tables.
In the trigger, we create a concatenated string of the old values by
casting them as follows:
CAST(O.MYDATE AS CHAR(30))
When directly updating date fields in the main table, the logged value
gets saved in the format YYYY-MM-DD as expected.
But, when we perform the update through a stored procedure, the date
values are saved as MM/DD/YYYY.
This causes our log parser to think the values are different, when they
are actually the same values represented in a different format.
We discovered that the CHAR( ) function has an optional parameter and
ISO can be used to explicitly designate the way dates are converted.
Question 1:
Does anyone know why the trigger would cast differently when it is
activated from a direct table update vs. a stored procedure?
Question 2:
Is there a database setting that can be used to set the default
conversion method to ISO when casting DATE to CHAR. This would save us
the trouble of recoding all of our triggers to explicitly specify the
ISO option.
Thanks,
Bob
Example:
DROP TABLE DATETEST;
CREATE TABLE DATETEST(ID BIGINT NOT NULL GENERATED BY DEFAULT AS
IDENTITY,
MYDATE DATE);
DROP TABLE DATETESTLOG;
CREATE TABLE DATETESTLOG(ID BIGINT NOT NULL GENERATED BY DEFAULT AS
IDENTITY,
MYDATA VARCHAR(1000),
MYDATAISO VARCHAR(1000),
TS TIMESTAMP);
CREATE TRIGGER DATETEST_ULog AFTER UPDATE
ON DATETEST
REFERENCING OLD
AS O
FOR EACH ROW
MODE DB2SQL INSERT INTO DATETESTLOG(
MYDATA, MYDATAISO, TS)
VALUES(CAST(O.MYDATE AS CHAR(30)), CHAR(O.MYDATE, ISO), CURRENT
TIMESTAMP)
;
DROP PROCEDURE RG.DATETESTCHG;
CREATE PROCEDURE RG.DATETESTCHG(IN PID BIGINT, CHGAMT INT)
SPECIFIC DATETESTCHG
RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
BEGIN
UPDATE DATETEST SET MYDATE = MYDATE + CHGAMT DAY
WHERE ID = PID;
END
;
INSERT INTO DATETEST(MYDATE) VALUES('1967-11-05');
INSERT INTO DATETEST(MYDATE) VALUES(CURRENT DATE);
UPDATE DATETEST SET MYDATE = MYDATE + 1 DAY
WHERE ID = 1;
CALL RG.DATETESTCHG(2, 1);
SELECT * FROM DATETEST;
SELECT * FROM DATETESTLOG;
