Desperately seeking help formatting Unix time to date and time

[Former Member]

Post Author: dblackerby

CA Forum: Formula

I'm a complete newbie to all of this and I have to complete a report by Thursday for my VP.I'm using Crystal Report XI and I'm trying to pull information from CA's Service Desk. When I pull a date for a change order, I believe it is pulling Unix time. I desperately need to know how to format the field so it's "human-readable" in the date and time format.Can someone help me PLEASE!!Thanks.

[Former Member]

Post Author: kbrinton

CA Forum: Formula

Hi,

I am using CA service desk as well. you have to take the formula I gave you and replace it with your date field. So for instance, if you want to use the field for open date on a ticket you would create a formula called opendate and put this in it PDMTimeToDateTime ({call_req.open_date}) then if you want to use that time as the parameter you would create 2 date parameters one for start date and one for end date. If you are trying to retrieve incidents from CA Then in your record selection you would use something like this:

{call_req.type} = "I" and{@OpenDate} >= {?Reporting Start Date} and{@OpenDate} <= {?Reporting End Date}

If you need further help I can send you a CA report if you want.

[Former Member]

Post Author: codyj

CA Forum: Formula

I think you were replying to me, so I tried that formula. I get "The remaining text does not appear to be part of the formula" and it highlights the whole formula. BTW, I am using CR XI Developer.

Thanks.

[Former Member]

Post Author: kbrinton

CA Forum: Formula

Try this very simple formula to convert the time. PDMTimeToDateTime ({table.date field})

[Former Member]

Post Author: codyj

CA Forum: Formula

Another newbie here.

In your example: CDatetime (( - 21600) / 86400 + 25569), what does / 86400 + 25569 do? I am trying to convert a table in GMT to PST, so I know that I will change -21600 to -28800.

[Former Member]

Post Author: SKodidine

CA Forum: Formula

If it is GMT then just subtract 21,600 seconds from it to give you CDT which is -6 GMT. If you have to, make sure you account for DST.

CDatetime (( - 21600) / 86400 + 25569);

or you can do add this to your existing formula:

datetimevar tempdt := DateAdd ("s", +{DateTime.Field},Date (1970,1,1) );

dateadd("s",-21600,tempdt);

[Former Member]

Post Author: dblackerby

CA Forum: Formula

The command referenced in one of the threads helped me format the date and time, however, it's GMT. Does anyone know how I would adjust this command to pull EDT or CDT?DateAdd ("s", +{DateTime.Field},Date (1970,1,1) ) Thanks!!

[Former Member]

Post Author: dblackerby

CA Forum: Formula

I'm really trying NOT to panic, but I'm not a SQL person and I'm not a Unix person...and I know nothing of the commands...so it's a bit stressful.I will look at the threads and see if I can figure it out.Thanks for the help.Denise

[Former Member]

Post Author: SKodidine

CA Forum: Formula

It is only Monday still, stop panicking

Check out these two threads. If the solutions suggested do not work, then please post some data so that it gives us a better idea.

http://technicalsupport.businessobjects.com/cs/forums/permalink/594/587/ShowThread.aspx#587http://te...