RE: numeric conversion
Uday
The problem appeared interesting, so here is a short SP I wrote to do this.
It converts a integer in the form yyyymmdd to a date mm/dd/yyyy (the latter
depends on your DBDATE setting, mine is set to MDY2/).
create procedure int2date (ndate integer) returning date;
define year char(4);
define month char(2);
define day char(2);
define sdate char(8);
define ddate char(10);
let sdate = ndate;
let year = sdate[1,4];
let month = sdate[5,6];
let day = sdate[7,8];
let ddate = trim(month) || "/" || trim(day) || "/" || trim(year);
return ddate;
end procedure;
I checked that the return value is indeed a date by executing the following
sql:
select * from testtable
19990701select int2date(ndate) from testtable;
07/01/1999
select today - int2date(ndate) from testtable;
53
HTH
Sujit
"Marichamy, Udaykumar" <Uday.Marichamy@usoncology.com> on 08/23/99 12:51:26
PM
To: informix-list@iiug.org
cc: (bcc: Sujit Pal)
Subject: RE: numeric conversion
I am interested in a solution too. We have stored some dates as integer to
store dates in the format yyyymm. The user prefered the integer type for
easier manipulation from the front-end.
For example, how to determine the year from the integer value 199907.
Uday
-----Original Message-----
From: Obnoxio The Clown [mailto:obnoxio@hotmail.com]
Sent: Monday, August 23, 1999 3:45 PM
To: Michele.Orcutt@windmere.com; informix-list@iiug.org
Subject: Re: numeric conversion
From: Michele Orcutt <Michele.Orcutt@windmere.com>
>
>Hello. I have what I think is a simple question, only I'm having a lot of
>trouble finding an answer. All my dates are saved as type numeric. I would
>like
>to convert some of the fields to "date". I cannot find the correct syntax
>to
>perform this conversion. Can it be done?
Why are your dates stored as numerics? There is a datatype called DATE, and
it's there to store dates. So that you don't have to do this sort of thing.
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
Sujit.Pal@bankofamerica.com wrote:
> The problem appeared interesting, so here is a short SP I wrote to
> do this. It converts a integer in the form yyyymmdd to a date
> mm/dd/yyyy (the latter depends on your DBDATE setting, mine is set
> to MDY2/).
>
> create procedure int2date (ndate integer) returning date;>
> define year char(4);
> define month char(2);
> define day char(2);
> define sdate char(8);
> define ddate char(10);
>
> let sdate = ndate;
> let year = sdate[1,4];
> let month = sdate[5,6];
> let day = sdate[7,8];
>
> let ddate = trim(month) || "/" || trim(day) || "/" || trim(year);
And, if your version of the server doesn't support TRIM, you should
be able to use (I haven't actually tested it, but I'd be very surprised
if it did not work):
let ddate = MDY(month, day, year);
In fact, even if you do have TRIM, you can use MDY(), and it gets
around problems with DBDATE -- it works the same regardless of the
setting of DBDATE. In fact, you could probably even just return
the result of calling MDY() without bothering with an explicit
DATE variable.
> return ddate;
>
> end procedure;
>
> I checked that the return value is indeed a date by executing the
> following sql:
>
> select * from testtable
> 19990701> select int2date(ndate) from testtable;
> 07/01/1999
> select today - int2date(ndate) from testtable;
> 53
>
> "Marichamy, Udaykumar" <Uday.Marichamy@usoncology.com> wrote:
>>I am interested in a solution too. We have stored some dates as
>> integer to store dates in the format yyyymm. The user prefered
>> the integer type for easier manipulation from the front-end.
>>
>> For example, how to determine the year from the integer value 199907.
>
>Obnoxio The Clown <obnoxio@hotmail.com> wrote:
>> From: Michele Orcutt <Michele.Orcutt@windmere.com>
>> >Hello. I have what I think is a simple question, only I'm having a
>> >lot of trouble finding an answer. All my dates are saved as type
>> >numeric. I would like to convert some of the fields to "date". I
>> >cannot find the correct syntax to perform this conversion. Can it
>> >be done?
>>
>> Why are your dates stored as numerics? There is a datatype called
>> DATE, and it's there to store dates. So that you don't have to do
>> this sort of thing.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN
#include <disclaimer.h>