Reporting Services and Epoch time (MySQL)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Reporting Services and Epoch time (MySQL)

Author

Topic

Daza
Starting Member

1 Post

Posted - 2008-09-17 : 18:53:58

Hi There,

The company I work for has recently purchased some Help Desk software which runs on a MySQL Database. Now, I would like to generate reports using Reporting Services 2005.

The problem I am facing is that the date field (integar) within the MySQL DB is a set of numbers (E.g. 1220560003) – this set of numbers (seconds) is based on Unix Time (Epoch time), starting from 01 01 1970.

I have managed to convert this time into a readable format using the SQL function DATEADD.

Here is the query I am running:

Select t1.Ticketid, Contents, t3.title as Module, t2.subject, t1.fullname as Staff,
cast(dateadd(ss,-14440, Dateadd(ss,t1.dateline,'19700101') +1) as smalldatetime) AS LoggedDate, t5.Title as Client
From swtickets t1, swticketposts t2, swdepartments t3, swusergroups t5, swusers t6
Where t1.ticketstatusid = @TicketStatus
and t5.usergroupid = @Client
and (t1.dateline >= @StartDate) and (t1.dateline-1 <=@EndDate)
and t1.ticketid = t2.ticketid
and t2.userid = t6.userid
and t6.usergroupid = t5.usergroupid
and t3.departmentid = t1.departmentid
and t2.dateline = (select min(dateline) from swticketposts t4
where t4.ticketid = t2.ticketid)
order by t2.dateline desc

The LoggedDate field then displays as 2008-07-21 12:15:00

Without the cast function it currently displays as 2008-07-21 12:15:13.000

The problem I am facing is within Reporting Services for users to have the ability to select a date using the Calendar boxes the fields need to be datetime. When I setup the parameters within Reporting Services for @StartDate and @Enddate and make them datetime, the report fails to run – with the error:

Arithmetic overflow error converting expression to data type datetime

So I guess what I am asking is:

Would anyone know what Parameter I could place in my Report for @StartDate and @EndDate to enable my report to work?

I tried this, but it didn't seem to work -
=DateTime.Parse(Parameters!StartDate.Value).ToString("dd MMM yyy")

After researching on the net, the general consenus is to utilise the DateTime object which is part of .net to get my parameter to work.

I have created multiple reports so far, but not based on any type of date range.

Thanks,

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-17 : 19:07:04

this site is for microsoft ms sql, try posting here sitepoint.com/forums or dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources