decimal to date
Frank Swarbrick
One can use the DECIMAL function to convert a date to a decimal.
For instance
values decimal(current_date)
returns 20080528.
Is there an easy way to convert the decimal value back to a date?
I can't use DATE(<decimal-value>) because it expects the decimal value to be
the number of days since Jan 1, 0001.
I'm sure I could write a function to do what I need, but I don't want to
waste my time if it already exists.
DB2/LUW 9.5.
Thanks,
Frank
The Boss
This groups archive (in Google) shows a nice solution (amongst others) by
Tonkuma:
DATE(TRANSLATE('ABCD-EF-GH',DIGITS(MYDATE),'ABCDEFGH'))
[where MYDATE is your decimal-date-field].
The thread was called "Convert DECIMAL to DATE" and was started on 20060308
;o)
Cheers!
--
Jeroen
Serge Rielau
> Frank Swarbrick wrote:
>> I've probably asked this before, but I can't remember the answer!
>>
>> One can use the DECIMAL function to convert a date to a decimal.
>> For instance
>> values decimal(current_date)
>> returns 20080528.
>>
>> Is there an easy way to convert the decimal value back to a date?
>> I can't use DATE(<decimal-value>) because it expects the decimal
>> value to be the number of days since Jan 1, 0001.
>>
>> I'm sure I could write a function to do what I need, but I don't want
>> to waste my time if it already exists.
>>
>> DB2/LUW 9.5.
>>
>> Thanks,
>> Frank
>
> This groups archive (in Google) shows a nice solution (amongst others) by
> Tonkuma:
>
> DATE(TRANSLATE('ABCD-EF-GH',DIGITS(MYDATE),'ABCDEFGH'))
converting the beast to a string.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Frank Swarbrick
<6a6fipF...@mid.individual.net>,
Thanks Serge and 'Boss'.
This is what I came up with:
CREATE FUNCTION date_from_decimal (dec_date DECIMAL(8))
RETURNS DATE
SPECIFIC date_from_decimal8
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN DATE(TIMESTAMP_FORMAT(DIGITS(dec_date),'YYYYMMDD'));
Works like a charm.
Still think that something like it should be built in to DB2, but perhaps
it's not that common...
Frank
Frank Swarbrick
<483E7B60.6...@efirstbank.com>,
I do have another sort of related question...
Is there a way to automatically issue a SET PATH statement whenever a user
connects?
I want to place the date_from_decimal function in a particular schema that
is not named after any particular user (CREATE FUNCTION
fb_func.date_from_decimal), but I also want to allow any user to invoke it
without needing to use the schema qualifier. I know that I can have each
application issue a SET PATH statement prior to invoking the function (SET
PATH = fb_func, CURRENT PATH), but it would be nice to have DB2 do this
automatically.
Thanks,
Frank
jefftyzzer
wrote:
> >>> On 5/29/2008 at 9:46 AM, in message
>
>
>
>
> Frank Swarbrick<Frank.Swarbr...@efirstbank.com> wrote:
> >>>> On 5/28/2008 at 7:35 PM, in message
If the users are connecting via CLP, I don't know of another way other
than setting it each time. If they're connecting via other means, you
could try setting the current (function) path via the cli.ini. Also--
again depending on how users are connecting--the application server/
ORM layer/JDBC layer may have its own ini file that you could put the
entry (or its equivalent) in.
--Jeff
Serge Rielau
>> I want to place the date_from_decimal function in a particular schema that
>> is not named after any particular user (CREATE FUNCTION
>> fb_func.date_from_decimal), but I also want to allow any user to invoke it
>> without needing to use the schema qualifier. I know that I can have each
>> application issue a SET PATH statement prior to invoking the function (SET
>> PATH = fb_func, CURRENT PATH), but it would be nice to have DB2 do this
>> automatically.
Keeps popping up, but never (so far) made the cut.
Frank Swarbrick
<4154d8a4-908d-49b7...@i18g2000prn.googlegroups.com>,
jefftyzzer<jefft...@sbcglobal.net> wrote:
> On May 29, 9:14 am, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
> wrote:
>>
>> Is there a way to automatically issue a SET PATH statement whenever a
> user
>> connects?
>>
>> I want to place the date_from_decimal function in a particular schema
> that
>> is not named after any particular user (CREATE FUNCTION
>> fb_func.date_from_decimal), but I also want to allow any user to invoke
> it
>> without needing to use the schema qualifier. I know that I can have
> each
>> application issue a SET PATH statement prior to invoking the function
> (SET
>> PATH = fb_func, CURRENT PATH), but it would be nice to have DB2 do this
>> automatically.
>
> than setting it each time. If they're connecting via other means, you
> could try setting the current (function) path via the cli.ini. Also--
> again depending on how users are connecting--the application server/
> ORM layer/JDBC layer may have its own ini file that you could put the
> entry (or its equivalent) in.
We'll be connecting either from JDBC (via Websphere) and from host DRDA
client applications.
Thanks for the ideas.
Frank
jefftyzzer
wrote:
> >>> On 5/29/2008 at 11:15 AM, in message
>
Frank,
In re: WebSphere, I just spoke to one of our administrators, who gave
me some information that might be helpful to you: You'll want to set
the currentFunctionPath Custom Property within your Data Source, which
is itself within the JDBC Providers link in the Resources area of the
WS Admin Console.
HTH,
--Jeff
Frank Swarbrick
<0996127a-a4af-492f...@a32g2000prf.googlegroups.com>,
jefftyzzer<jefft...@sbcglobal.net> wrote:
> In re: WebSphere, I just spoke to one of our administrators, who gave
> me some information that might be helpful to you: You'll want to set
> the currentFunctionPath Custom Property within your Data Source, which
> is itself within the JDBC Providers link in the Resources area of the
> WS Admin Console.
Thanks.
Frank