Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2007
    Posts
    6

    Question Unanswered: Convert number to date

    Help Please!!

    I'm trying to pull some dates from a database using Crystal XI but the actual dates are number fields. For example one field (lastmodifieddate) is displayed as 1,119,354,970,984.00

    Some other date fields in the database are simply displayed as 523, 725 etc...

    I'm assuming I need to create a formula which will convert the numbers to dates but I have no clue what that formula should be.

    This is the formula that I have so far...
    dateadd("s",({datefield} - 18000),datetime(1970,1,1,0,0,0))
    which returns a date of 4/20/1918 6:25:38PM. This isn't the correct date though.

    Thx in advance.
    Last edited by laplantec; 05-15-08 at 14:34.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nor do we!

    As you've noticed 1,119,354,970,984.00 is NOT A DATE!

    Do you know what date it is trying to represent and why?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Posts
    6
    Most of the dates in the database are either history logs or last activity dates

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Mhmm...

    So 1,119,354,970,984.00 is what date?
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2003
    Posts
    156
    Are you reporting on the Repository? The BO repository is time in seconds since 15-JAN-1970. So it's 15-JAN-1970 + ({yourdatefield}/60*60*24)

    to_date('15/12/1970','DD/MM/YYYY') + (OBJ_M_ACTOR.M_ACTOR_N_LAD/86400)

    Even if it's not the BO repo you're reporting on, where do you get the 1800 from? Are you also sure it's seconds as of 01-JAN-1970?

    Whichever your reference date is...

    {yourreferencedate} + ({yourdatefield}/(60seconds*60minutes*24hours))

    or

    TO_DATE('15-JAN-1970', 'DD/MM/YYYY)' + ({yourdatefield}/86400)
    rgs,

    Ghostman

  6. #6
    Join Date
    Dec 2007
    Posts
    6
    I'm not certain what date 1,119,354,970,984.00 is but I know it's definently not the correct restult that I'm getting back.

    Ghostman, I created the formula that you provided Date(01/05/1970) + ({O_CO_PROJECT.CP_P_M_CREATIONTIME}/60*60*24) but Crystal returns an error message when it's run "the number of days is out of range". I am reporting on the repository. The 1800 comes from a seperate formula that I used on another database which works on that specific database but not this one for some reason. I'm assuming my referencedate is incorrect?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    We can't guess what your value:

    one trillion one hundred nineteen billion three hundred fifty-four million nine hundred seventy thousand nine hundred eighty-four

    should be in date form; my guess is that it can't be done due to arithmetic overflow (it's just too big-a-number)!
    George
    Home | Blog

  8. #8
    Join Date
    Dec 2007
    Posts
    6
    The vendor themselves can't tell me the value either as they had purchased the application from another company. I'm getting date values from the formulas that were provided except they are way off. I found this but it gives me an error "date must be between year 1 and year 9999"...
    Once again, I hit a wall when trying to integrate all my databases here at work. This time, I was stymied by the fact that even though Cisco CCM runs it’s database on MS SQL server, the system does not use native date/time formats. Instead it decides to use the industry standard… number of seconds since January 1st, 1970. So I have a huge long integer. No problem right? I’ll just put it through Crystal’s formula engine. Except I get an error telling me the year is out of range.

    Turns out the Crystal Date functions are based upon DAYS, not seconds. and the base date is 1900, not 1970. So, in order to make this work, I need to convert our integer from seconds to days, and then offset our number by 70 years. So what’s the solution?

    CDateTime ({call manager date value}/(24*60*60)+(Date (1970, 1, 1)-Date (1900, 1, 1)))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •