decimal to date

[Frank Swarbrick]

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

[The Boss]

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

[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]

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

In DB2 9.5 you can also use TIMESTAMP_FORMAT() (aka TO_DATE) after
converting the beast to a string.

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

[Frank Swarbrick]

>>On 5/28/2008 at 7:35 PM, in message
<6a*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:

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

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

[Frank Swarbrick]

>>On 5/29/2008 at 9:46 AM, in message
<48******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

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

<6a*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:

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

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

[jefftyzzer]

On May 29, 9:14 am, "Frank Swarbrick" <[e-mail]>
wrote:

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

<[e-mail]>,

Frank Swarbrick<[e-mail]:

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

<[e-mail]>,
Serge Rielau<[e-mail]:

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

[Serge Rielau]

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

[Frank Swarbrick]

>>On 5/29/2008 at 11:15 AM, in message
<41**********************************@i18g2000prn. googlegroups.com>,
jefftyzzer<je********@sbcglobal.netwrote:

On May 29, 9:14 am, "Frank Swarbrick" <[e-mail]>
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

[jefftyzzer]

On May 29, 4:07 pm, "Frank Swarbrick" <[e-mail]>
wrote:

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

<[e-mail]>,

jefftyzzer<[e-mail]:

On May 29, 9:14 am, "Frank Swarbrick" <[e-mail]>
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

[Frank Swarbrick]

>>On 5/30/2008 at 12:57 PM, in message
<09**********************************@a32g2000prf. googlegroups.com>,
jefftyzzer<je********@sbcglobal.netwrote:

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