decimal to date

[Database Administrator]

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() 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

[Database Administrator]

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() 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'))

[where MYDATE is your decimal-date-field].

The thread was called "Convert DECIMAL to DATE" and was started on 20060308
;o)

Cheers!

--
Jeroen

[Database Administrator]

>>> On 5/28/2008 at 7:35 PM, in message
<6a6fipF34p4juU2@mid.individual.net>,
Serge Rielau wrote:
> The Boss wrote:
>> 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() 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'))
> In DB2 9.5 you can also use TIMESTAMP_FORMAT() (aka TO_DATE) after
> converting the beast to a string.

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

[Database Administrator]

>>> On 5/29/2008 at 9:46 AM, in message
<483E7B60.6F0F.0085.0@efirstbank.com>,
Frank Swarbrick wrote:
>>>> On 5/28/2008 at 7:35 PM, in message
> <6a6fipF34p4juU2@mid.individual.net>,
> Serge Rielau wrote:
>> The Boss wrote:
>>> 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() 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'))
>> In DB2 9.5 you can also use TIMESTAMP_FORMAT() (aka TO_DATE) after
>> converting the beast to a string.
>
> 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...

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

[Database Administrator]

On May 29, 9:14 am, "Frank Swarbrick"
wrote:
> >>> On 5/29/2008 at 9:46 AM, in message
>
> <483E7B60.6F0F.008...@efirstbank.com>,
>
>
>
> Frank Swarbrick wrote:
> >>>> On 5/28/2008 at 7:35 PM, in message
> > <6a6fipF34p4j...@mid.individual.net>,
> > Serge Rielau wrote:
> >> The Boss wrote:
> >>> 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() 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'))
> >> In DB2 9.5 you can also use TIMESTAMP_FORMAT() (aka TO_DATE) after
> >> converting the beast to a string.
>
> > 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...
>
> 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

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

[Database Administrator]

jefftyzzer wrote:
>> 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.
You want a .login. Yeah it's on my wish list, too.
Keeps popping up, but never (so far) made the cut.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Database Administrator]

>>> On 5/29/2008 at 11:15 AM, in message
<4154d8a4-908d-49b7-90f4-ae3d1e500774@i18g2000prn.googlegroups.com>,
jefftyzzer wrote:
> On May 29, 9:14 am, "Frank Swarbrick"
> 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.
>
> 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.

We'll be connecting either from JDBC (via Websphere) and from host DRDA
client applications.

Thanks for the ideas.

Frank

[Database Administrator]

On May 29, 4:07 pm, "Frank Swarbrick"
wrote:
> >>> On 5/29/2008 at 11:15 AM, in message
>
> <4154d8a4-908d-49b7-90f4-ae3d1e500...@i18g2000prn.googlegroups.com>,
>
>
>
> jefftyzzer wrote:
> > On May 29, 9:14 am, "Frank Swarbrick"
> > 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.
>
> > 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.
>
> We'll be connecting either from JDBC (via Websphere) and from host DRDA
> client applications.
>
> Thanks for the ideas.
>
> Frank

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

[Database Administrator]

>>> On 5/30/2008 at 12:57 PM, in message
<0996127a-a4af-492f-a8cf-1c289565542a@a32g2000prf.googlegroups.com>,
jefftyzzer 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