By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,652 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,652 IT Pros & Developers. It's quick & easy.

decimal to date

P: n/a
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
Jun 27 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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
Jun 27 '08 #2

P: n/a
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
Jun 27 '08 #3

P: n/a
>>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

Jun 27 '08 #4

P: n/a
>>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

Jun 27 '08 #5

P: n/a
On May 29, 9:14 am, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
wrote:
>On 5/29/2008 at 9:46 AM, in message

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

Frank Swarbrick<Frank.Swarbr...@efirstbank.comwrote:
>>On 5/28/2008 at 7:35 PM, in message
<6a6fipF34p4j...@mid.individual.net>,
Serge Rielau<srie...@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
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
Jun 27 '08 #6

P: n/a
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
Jun 27 '08 #7

P: n/a
>>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" <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.

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

Jun 27 '08 #8

P: n/a
On May 29, 4:07 pm, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
wrote:
>On 5/29/2008 at 11:15 AM, in message

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

jefftyzzer<jefftyz...@sbcglobal.netwrote:
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.
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
Jun 27 '08 #9

P: n/a
>>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

Jun 27 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.