Using MySQL date fields in Crystal Reports XI

[EP44]

I'm new to MySQL & Crystal, and am trying to display a date in a CR that is defined as bigint(20) in MySQL, but it just comes out as numerics (of course!).

So the question is what do I need to do to get CR to see this as a date field?

Many thanks

 

[Turkbear]

Hi,
You will need to create a formula the decodes whatever MySql does to store a date in a BigInt data type..Probably involves the # of days since some base date..





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

 

[EP44]

Many thanks Turkbear,

it seems that this is a MySQL timestamp field that contains the number of seconds since 1 Jan 1970 00:00:00 GMT.

I'm completely new to CR, so I'd really appreciate full instructions and the formula code I need to convert this into a real date & time.

EP44

 

[elsenorjose]

Do you have write access to the table? If you do you could create a new column 'friendly_date' or something using

FROM_UNIXTIME (your_unix_timestamp_field)

This is a built in MySQL function that converts the UNIX time to human readable date time. Example:

SELECT FROM_UNIXTIME(875996580);
Result would be : '1997-10-04 22:23:00'

If not, you can try:

DateDiff("s", #1 Jan 1970#, Date1) where Date1 is your UNIX time field.

Lastly, you can download this:

http://www.viksoe.dk/code/u2lwin32.htm

 

[samiam2006]

Since we're in an environment that is locked down and doesn't allow any non-approved software, I came up with the following formula:
DateTime(1970,1,1,0,0,0) + ({#_of_seconds}/86400)

It converts the #_of_seconds to days and a time fraction.