Strange date/time field need to covert to a Date

[careymw]

I'm using Crystal reports 2008 with a Oracle DB for this report.
I have a field called Completed_Date_Time if I display this field in the layout it looks like this.
1,319,637,601.00
This should be a date time field and I need to parse it out to a date so I can pull from a parameter date range.
Can anyone help me get this into a usable date field for Crystal Reports?

 

[lbass]

You need to first tell use what actual date this value corresponds to.

-LB

 

[Turkbear]

Hi,
How is that field defined in the table? Is it a Date,Timestamp or Number?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

This might be the number of seconds since 1/1/1970, but you should find out for sure. If it is, then you could use a formula like:

dateadd("s",{table.completed_date_time},date(1970,1,1))

-LB

 

[careymw]

I am trying to find out what the date actually is...it's a new development so I don't have access to the tables...I'm only seeing what I can see through Crystal. I also am not sure on the field is defined in the table...hoping to have some answers very soon.

@ Lbass - there was mention of the Jan 1 1970 date in a query I saw for some other conversions....I will sew what I can get with your suggestion. Thanks so much you guys are a huge help.

 

[SkipVought]

FTI, assuming that it is seconds since 1/1/1970, the date would fall on 10/26/2011.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[careymw]

When I used
dateadd("s",{EGPLR_ACTIVITY_SNAPSHOT.COMPLETION_DATE_TIME},date(1970,1,1))
The results I got were 12/13/1901 which seemed weird...how are you arriving at 10/26/2011?

 

[SkipVought]

are you SURE that the value of EGPLR_ACTIVITY_SNAPSHOT.COMPLETION_DATE_TIME is 1319637601?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[careymw]

Hahaha...no. I used a different number then I was looking at in the table....
1,315,575,091,648 is what I am currently seeing....I am converting to12/13/1901

 

[lbass]

What's funny? Where did you get the first number then? Maybe this is in milliseconds, which datadd can't handle. If so then use:

dateadd("s",{EGPLR_ACTIVITY_SNAPSHOT.COMPLETION_DATE_TIME}/1000,date(1970,1,1))

-LB

 

[SkipVought]

1,315,575,091,648???

That value of seconds is approximately 41,700 YEARS???

Think that you are getting some sort of overflow, that "changes" the sign bit.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[careymw]

Sorry, it was funny and I was laughing at myself that I was just looking at 2 different pieces of information. When he asked me if I was sure that the number I was looking at was the same.
I am working with the Completed_Date_Time field in one table and DATE_TIME_INSEC field in another table....so when I displayed my example for you it came from the other table....they are in the same format.

{EGPLR_ACTIVITY_SNAPSHOT.COMPLETION_DATE_TIME} = 1,315,575,091,648
{EGSSR_SMY_ARTICLE.DATE_TIME_INSEC} = 1,319,637,601

 

[SkipVought]

Well then, it seems that seconds since 1/1/1970 is not what your data represents.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Milliseconds might work.

What we REALLY need is what CALENDAR DATES these huge numbers represent.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lbass]

The two figures appear to be for different dates, so if these fields are returned in the same row, they do not refer to the same thing. I think you need to talk to whoever knows the specifics about the database fields.

-LB

 

[careymw]

No they are not for the same row at all they are completely different from each other...on e is for a completed activity and the other is the date an article was created. They will never be associated.
I've filed a ticket to find out more details on the fields. I just have to wait....
I was just hoping to be proactive it takes them longer to get back to me then it does to get some advice on here.
Thanks so much for the suggestions.