ssrs query

Posted on 2009-04-23
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
    LVL 51

    Expert Comment

    by:Mark Wills
    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

    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

    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

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

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    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.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now