Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


ssrs query

Posted on 2009-04-23
Medium Priority
Last Modified: 2013-11-27
I am new to using SSRS reporting, iw as using report builder. I was wondering how to convert the below report builder filter experessions to use in SSRS. I have looks and see there is this in SSRS but not sure how it is set up. CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119', CONVERT(CHAR(10), GETUTCDATE() - 5, 101) + '  12:00:00')) .
The below filter example i need to convert are:
Friday 18:00 to Monday 18:00:
AND(aTime  >= DATEDIFF(SECOND, #1/1/1970#, DATEADD(SECOND, 86400, TODAY())), aTime  < DATEDIFF(SECOND, #1/1/1970#, DATEADD(SECOND, 86400, (DATEADD(DAY, 3, TODAY())))))

end time in last 30 days:
eTime  >= DATEDIFF(SECOND, #1/1/1970#, DATEADD(DAY, -30, TODAY()))

any help is appreciated, also if you have a link to something that explains how to get the filter/qyert to work.
Question by:pgmtkl
  • 3
  • 2
LVL 51

Expert Comment

by:Mark Wills
ID: 24221752
What are "atime" and "etime" - they seem to be unix time stamps ?

How are you accessing the data at the moment ? Do you have a report able to print some data (regardless of filter), if so what is your datasource ?

So in SSRS what you need is PARAMETERS for the date so you can then use them to convert into seconds, or, have your query conert etime and atime into a date construct using date adds etc.

Can you show some sample data ?

Author Comment

ID: 24224516
they are unix time stamps. in report builder i would do (dateadd(day,-30,today). i access it through a sanpshot of data from the other server. do i need to use the datediff functon with dateadd?
LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 24256953
The way I would most likely be attacking this problem is to create a view over the data where I could have both the unix timestamps but also the convert versions of those time stamps, so instead of doing all the work on convert my actual date to an integer, I simply have to convert the unix integers to dates and then the rest of it becomes more straight forward...

So, in MS SQL parlance would be looking at : select convert(datetime,((atime / 86400) + 25567))     --where 25567 is the system zero date for SQL datetime of 1/1/1970

If you are lookinf for just a straight conversion, then  (and you will need to double check adding 86400 to getdate - that is adding 1 day)

AND(aTime  >= DATEDIFF(SECOND, '19700101', DATEADD(SECOND, 86400, GETDATE())) and aTime  < DATEDIFF(SECOND, '19700101', DATEADD(SECOND, 86400, (DATEADD(DAY, 3, GETDATE())))))

end time in last 30 days:

eTime  >= DATEDIFF(SECOND, '19700101', DATEADD(DAY, -30, GETDATE()))

Does that help ?

Author Closing Comment

ID: 31573794
Thanks so much, i was getting stuck on this since i am new to it.
LVL 51

Expert Comment

by:Mark Wills
ID: 24261509
Happy to help, and sorry about lossing the initial impetus on this thread. Thanks to CoolleoMod for sending out the reminder.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question