decimal to date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Swarbrick - 29 May 2008 00:17 GMT

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

Reply to this Message

The Boss - 29 May 2008 01:04 GMT

> I've probably asked this before, but I can't remember the answer!
>
[quoted text clipped - 14 lines]
> 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!

Signature

Jeroen

Reply to this Message

Serge Rielau - 29 May 2008 02:35 GMT

>> I've probably asked this before, but I can't remember the answer!
>>
[quoted text clipped - 19 lines]
>
> 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.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Frank Swarbrick - 29 May 2008 16:46 GMT

>>> On 5/28/2008 at 7:35 PM, in message

<6a6fipF34p4juU2@mid.individual.net>,

>>> I've probably asked this before, but I can't remember the answer!
>>>
[quoted text clipped - 22 lines]
> 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

Reply to this Message

Frank Swarbrick - 29 May 2008 17:14 GMT

>>> On 5/29/2008 at 9:46 AM, in message

<483E7B60.6F0F.0085.0@efirstbank.com>,

>>>> On 5/28/2008 at 7:35 PM, in message
> <6a6fipF34p4juU2@mid.individual.net>,
[quoted text clipped - 41 lines]
> 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

Reply to this Message

jefftyzzer - 29 May 2008 18:15 GMT

On May 29, 9:14 am, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
wrote:

> >>> On 5/29/2008 at 9:46 AM, in message
>
[quoted text clipped - 61 lines]
> 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

Reply to this Message

Serge Rielau - 29 May 2008 18:50 GMT

>> I want to place the date_from_decimal function in a particular schema that
>> is not named after any particular user (CREATE FUNCTION
[quoted text clipped - 3 lines]
>> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Frank Swarbrick - 30 May 2008 00:07 GMT

>>> On 5/29/2008 at 11:15 AM, in message

<4154d8a4-908d-49b7-90f4-ae3d1e500774@i18g2000prn.googlegroups.com>,

> On May 29, 9:14 am, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
> wrote:
[quoted text clipped - 21 lines]
> 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

Reply to this Message

jefftyzzer - 30 May 2008 19:57 GMT

On May 29, 4:07 pm, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
wrote:

> >>> On 5/29/2008 at 11:15 AM, in message
>
[quoted text clipped - 32 lines]
>
> 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

Reply to this Message

Frank Swarbrick - 30 May 2008 22:07 GMT

>>> On 5/30/2008 at 12:57 PM, in message

<0996127a-a4af-492f-a8cf-1c289565542a@a32g2000prf.googlegroups.com>,

> 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

Reply to this Message