Casting 1

[LaurenM]

Hey All, I'm currently converting some old Access '97 apps for use in our Oracle Database and I have a question. Once a month we get text file of payroll data. The file is not delimited in any fashion except in the sense that each line is a fixed length string, with various character positions matching various data fields. Now my question is this: Some of those fields are dates, and in the string they are just a 5 digit number,(ex: my birthdate which is 29/08/1974 is listed as 39140) now if I pass that number straight into a date field in a table, will I get an error, or will Oracle do the conversion for me? I have the conversion formula but I would really perfer to let Oracle do the work for me.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[LKBrwnDBA]

Oracle can do it, post the conversion formula


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Lauren,

I'd love to see the conversion formula for your dates since the intuitive value of "39140" would appear to be "days". If that is the case, where your birthday is "29/08/1974", the Base Date (i.e., "29/08/1974" - 39140) is "01-JUL-1967", leaving many of us "Old Duffers" as "pre-historic" (which is not too far from the truth).

So, please do post your algorithm.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LaurenM]

This is the Access VBA version, haven't gotten around to converting it to JAVA:

Public Function FDYconv(FDdate As Variant) As Variant
  Dim L As Long
  Dim N As Long
  Dim YYYY As Long
  Dim MM As Long
  Dim DD As Long
  Dim lngFDdate As Long
  Dim strDate As String  

  On Error GoTo Error_FDYconv
  '-- Return an empty string if input is Null or a ZLS --'
  If IsNull(FDdate) Then
    FDYconv = Null
  ElseIf FDdate = "" Or CLng(FDdate) = 0 Then
    FDYconv = Null
  Else
  '--     Otherwise perform all needed calculations    --'
    lngFDdate = CLng(FDdate)
    '-- This code works, don't ask me how.
    L = lngFDdate + 2471718
    N = Fix(4 * L / 146097)
    L = L - Fix((146097 * N + 3) / 4)
    YYYY = Fix(4000 * (L + 1) / 1461001)
    L = L - Fix(1461 * YYYY / 4) + 31
    MM = Fix(80 * L / 2447)
    DD = L - Fix(2447 * MM / 80)
    L = Fix(MM / 11)
    MM = MM + 2 - 12 * L
    YYYY = 100 * (N - 49) + YYYY + L
    'Concatenate the DD MM and YYYY variables into a string date'
    strDate = CStr(DD) + "/" + CStr(MM) + "/" + CStr(YYYY)    
    '-- Return the date if not equal to an empty date
    FDYconv = CDate(strDate)
  End If 

Error_FDYconv:
  Select Case Err.Number
    Case 0   ' Zero error code is a sign of success.
    Case 13  ' Something not a number was entered.
      FDYconv = #1/7/1867#
      Exit Function
    Case 94  ' A null value was provided.
      FDYconv = #1/7/1867#
      Exit Function
    Case Else
      MsgBox "another Error: " & Err.Number & Err.Description
  End Select
End Function

got it from a co-worker.

And I don't think that base date is 01-JUL-1967 another birthdate ('22-JUL-1954') gets translated to 31797.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

Lauren,

I have tried to convert the above Access VBA algorithm to Oracle's PL/SQL, but I cannot get the result to come out to the correct date.

Can you please have someone who is familiar with this algorithm explain it to you in non-technical, functional terms so that you can relate it to us?

If you can pass that information along to us, along with a record layout of your text file of payroll data, I'm certain that we can propose a working routine that transforms the payroll data into Oracle-usable data.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LaurenM]

I've talked to the person that I got the code from, she doesn't remeber where she got it from or how it works, when I asked her she pointed to her comment:

 '-- This code works, don't ask me how

I'm going to rewrite the code in JAVA and translate it there and then I can just cast it to a java.sql.Date type, and that should take care of things when I try to upload to our database. Thanks for the help.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[LKBrwnDBA]

Try this Oracle version:

Create or Replace Function FDYconv(FDdate Varchar2)
Return Varchar2
As
  L Number;
  N Number;
  YYYY Number;
  MM Number;
  DD Number;
  lngFDdate Number;
  strDate Varchar2(12);
  RetDate DATE;
Begin
  -- Return an empty Varchar2(32) if input is Null or a ZLS --
  If FDdate Is Null Or FDdate = '' Or To_Number(FDdate) = 0 Then
    Return Null;
  End If;
  --     Otherwise perform all needed calculations    --
  lngFDdate := To_Number(FDdate);
  -- This code works, don't ask me how.
  L := lngFDdate + 2471718;
  N := Trunc(4 * L / 146097);
  L := L - Trunc((146097 * N + 3) / 4);
  YYYY := Trunc(4000 * (L + 1) / 1461001);
  L := L - Trunc(1461 * YYYY / 4) + 31;
  MM := Trunc(80 * L / 2447);
  DD := L - Trunc(2447 * MM / 80);
  L := Trunc(MM / 11);
  MM := MM + 2 - 12 * L;
  YYYY := 100 * (N - 49) + YYYY + L;
  -- Concatenate the DD MM and YYYY variables into a Varchar2(12) date
  strDate := To_Char(DD,'FM00')||'/'||To_Char(MM,'FM00')||'/'||To_Char(YYYY,'FM0000');
  -- Return the date if not equal to an empty date
  Return strDate;
Exception
  When INVALID_NUMBER Then
    Raise_Application_Error(-20001, 'Invalid, Null Or Zero Number was provided!');
End;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

That works, LK! Good on Ya'! Hava

.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LaurenM]

Thank you, All. Both Santa, and LK have been very helpful.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

Lauren,

Just a question in passing...Are your dates to reside in Oracle from now on? If so, then you are far better off using Oracle DATE data type than some contrivance that requires nearly 40 lines of code to execute each time you want to encode/decode a date...That is a lot of wasted CPU cycles.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LaurenM]

The information is to stay in the database, once it is imported. So, once I get this JAVA app done, I will be doing a massive import from the past couple of years, and then once that is done it will only be run once a month.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan