ssrs query

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.
pgmtklAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
pgmtklAuthor Commented:
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?
0
 
pgmtklAuthor Commented:
Thanks so much, i was getting stuck on this since i am new to it.
0
 
Mark WillsTopic AdvisorCommented:
Happy to help, and sorry about lossing the initial impetus on this thread. Thanks to CoolleoMod for sending out the reminder.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.