443,652 Members | 1,358 Online
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() 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
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() 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 instancevalues 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 decimalvalue 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 wantto 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 RielauFrank 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 instancevalues 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 decimalvalue 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 wantto 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>>On 5/28/2008 at 7:35 PM, in message <6a*************@mid.individual.net>, Serge RielauThe 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 instancevalues 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 decimalvalue 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 wantto waste my time if it already exists.DB2/LUW 9.5.Thanks,FrankThis 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) afterconverting 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" wrote: >On 5/29/2008 at 9:46 AM, in message <483E7B60.6F0F.008...@efirstbank.com>, Frank Swarbrick>On 5/28/2008 at 7:35 PM, in message <6a6fipF34p4j...@mid.individual.net>, Serge Rielau>One can use the DECIMAL function to convert a date to a decimal.For instancevalues 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 decimalvalue 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 wantto waste my time if it already exists. >>DB2/LUW 9.5. >>Thanks,Frank >This groups archive (in Google) shows a nice solution (amongst others) byTonkuma: >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 thatis not named after any particular user (CREATE FUNCTIONfb_func.date_from_decimal), but I also want to allow any user to invoke itwithout needing to use the schema qualifier. I know that I can have eachapplication issue a SET PATH statement prior to invoking the function (SETPATH = fb_func, CURRENT PATH), but it would be nice to have DB2 do thisautomatically. 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 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 FUNCTIONfb_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 thisautomatically. 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" wrote: >On 5/29/2008 at 11:15 AM, in message <4154d8a4-908d-49b7-90f4-ae3d1e500...@i18g2000prn.googlegroups.com>, jefftyzzer 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

### This discussion thread is closed

Replies have been disabled for this discussion.