Fwd: SQL convert number into the date
Sent privately only by accident; not paying attention to To and Cc lines...
---------- Forwarded message ----------
From: Jonathan Leffler <jonathan.leffler@gmail.com>
Date: Wed, Nov 28, 2012 at 9:40 AM
Subject: Re: SQL convert number into the date
To: nsimba toni <ntoni.nsimba@esw-gmbh.de>
On Tue, Nov 27, 2012 at 3:01 AM, nsimba toni <ntoni.nsimba@esw-gmbh.de>wrote:
> I have a field in a table. This field is ar2.wbzdatum and is of type
> number.
> For example 90812. I want to convert this field in date format. the result
> must be the date 09.08.12. how can I make my SQL statement. What function I
> have to use. I have written so.
>
> SELECT Date(ar2.wbzdatum) as test
> this ist not correct.
>
I've seen numerous responses — but if it were my problem to solve, I'd
create and use a stored procedure to do the job, simply to avoid having to
write out ghastly expressions repeatedly (more than once).
CREATE PROCEDURE ddmmyy_date(ddmmyy INTEGER) RETURNING DATE AS datevalue; DEFINE mm INTEGER;
DEFINE dd INTEGER;
DEFINE yy INTEGER;
LET yy = MOD(ddmmyy, 100) + 2000;
LET mm = MOD(ddmmyy/100, 100);
LET dd = ddmmyy / 10000;
RETURN MDY(mm, dd, yy);
END PROCEDURE;
CREATE TEMP TABLE t_090812
(
i INTEGER NOT NULL,
d DATE NOT NULL
);
INSERT INTO t_090812 VALUES(90812, MDY(8,9,2012));
INSERT INTO t_090812 VALUES(290812, MDY(8,29,2012));
INSERT INTO t_090812 VALUES(301112, MDY(11,30,2012));
INSERT INTO t_090812 VALUES(301100, MDY(11,30,2000));
SELECT "PASS", i, ddmmyy_date(i), d
FROM t_090812
WHERE d = ddmmyy_date(i);
SELECT "FAIL", i, ddmmyy_date(i), d
FROM t_090812
WHERE d != ddmmyy_date(i) OR ddmmyy_date(i) IS NULL;
The related mmddyy_date() procedure is left as a (trivial) exercise for the
reader.
Having said and provided that, the best advice is still to convert the
INTEGER column to a DATE column:
ALTER TABLE ar2 ADD wbz_date DATE;
UPDATE ar2 SET wbz_date = mmddyy_date(wbz_datum);
ALTER TABLE ar2 MODIFY wbz_date DATE NOT NULL, DROP wbz_datum;RENAME COLUMN ar2.wbz_date AS wbzdatum;
Using weird types to store dates leads to weird problems because the weird
types aren't dates. And it doesn't much matter whether the weird type is
INTEGER or a CHAR variant; it leads to problems, sooner rather than later.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."