Auditing table timestamp

[almaler]

Hello Everyone,
I have installed auditing for Crystal Enterprise version 10 and am looking at the sample reports. I have noticed that the TimeStamp field in the CMS_AUDITTABLE table is formatted like this: 2006-03-27(16:41:26:640)
This is today's date at 4:41pm. The actual time that this event happened is today's date at 10:41am. Since this is 6 hours different it seems that the timestamp in the db is GMT and I need CST / CDT. What formula can I use to subtract 6 hours from the timestamp and still take into account daylight savings time?

 

[synapsevampire]

There's a UFL by Ido Millet, one of the strongest contributors here:

http://www.milletsoftware.com/CUT_Light.htm

-k

 

[almaler]

I really don't have the option to purchase anything. I need a way to do this with the out-of-the-box Crystal Reports designer.

 

[synapsevampire]

This is by design, all logging is done using GMT.

So you'll have to write your own timezone converter I guess.

Each user could have their own timezone, not just CST in theory, so this could get tricky.

If you're using a recent version of an Oracle database, it has some timezone conversion functions, if you're using the standard SQL Server, there are UDF's, as in:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28712

So you might be able to build out a View or SP to do so.

-k

 

[almaler]

So there is no way in Crystal Reports 10 to take a date and minus 6 hours?

 

[BobSuruncle]

If the TimeStamp field is an actual DateTime field, then you could use
DateAdd('h',-6,{Table.field})

If the TimeStamp field is stored as a string, you'd need to convert it to a DateTime first.


Bob Suruncle

 

[almaler]

Thank you, Bob - that is exactly what I needed.

 

[synapsevampire]

I don't think that works for daylight savings time, but it's an obvious cheat.

I don't know the daylight savings time periods, but you coudl elaborate slightly on this for the periods, as in:

if {table.date} >= cdate(year({table.date}),month(<month value of the START of daylight savings>),day(<year value of the START of daylight savings>))
and
{table.date} <= cdate(year({table.date}),month(<month value of the END of daylight savings>),day(<year value of the END of daylight savings>))
then
DateAdd('h',-7,{Table.field})
else
DateAdd('h',-6,{Table.field})

Again, I don't know the dates nor the logic of the amount to add, but this should handle your CST needs.

Just make sure you don't have anyone using the system outside of the timezone.

-k

 

[almaler]

Thanks for expounding on that. That really helps out for daylight savings time!