asked on

SSRS

I am trying to create a ssrs report and the datetime stamp from the datasource is in unix and needs to be converted from gmt time. I tried using the below but get a timeout and it doesnt work. How does this need to me modified?
WHERE (h_Start_Time >= CONVERT(BIGINT, DATEDIFF(S, '19700101', DATEADD(SECOND, 86400, GETDATE())))) AND
AND (h_Start_Time <= CONVERT(BIGINT, DATEDIFF(S, '19700101', DATEADD(day, 3, GETDATE()))))

dbaSQL

expert bhess1 has a very good solution, this may be of help:

https://www.experts-exchange.com/questions/22585773/GMT-UTC.html

pgmtkl

ASKER

I notice in a lot of places that the dateadd and datediff are in the select statement. can they be in the where statement?

dbaSQL

yes, i believe so.

i think maybe if you're going from GMT to CST, you could just subtract 6. you'd need to check for DST (daylight savings time). check the current date, if it is dst, subtract 5. otherwise subtract 6

WHERE DATEPART(hh,DATEADD(hh, -6, h_Start_Time)) >=

pgmtkl

ASKER

Is datepart the substitue for getdate?

dbaSQL

yes and no. DATEPART is representing the part of the data you wish to return. in this case, it's the hours. hour = hh

you are subtracting 6 from the hour in h_Start_Time

see BOL, DATEPART

pgmtkl

ASKER

ok. i was able to get the data but notice it is displaying with the wrong timestamp- am when it should be pm (vice versa). do i need to add additional format to the text/data section?
=dateadd("h",7,Fields!Open_Time.Value)

ASKER CERTIFIED SOLUTION

dbaSQL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial