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()))))
pgmtklAsked:
Who is Participating?
 
dbaSQLConnect With a Mentor Commented:
i'm not sure exactly what you're running, but yes, to get AM or PM, you can just do this:

SELECT RIGHT(CONVERT(VARCHAR(24),getdate(), 0), 7)
or
SELECT DATENAME(hh, getdate()) + ':' + DATENAME(mi, getdate()) + case when datepart(hh, getdate()) > 12 then ' PM' else ' AM' end AS yourTimestamp

or even this:
SELECT  (SUBSTRING(CONVERT(VARCHAR(30), GETDATE(), 109), 13, 8) +
SUBSTRING(CONVERT(VARCHAR(30), GETDATE(), 109), 25, 2))
0
 
dbaSQLCommented:
0
 
pgmtklAuthor Commented:
I notice in a lot of places that the dateadd and datediff are in the select statement. can they be in the where statement?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dbaSQLCommented:
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)) >=
0
 
pgmtklAuthor Commented:
Is datepart the substitue for getdate?
0
 
dbaSQLCommented:
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
0
 
pgmtklAuthor Commented:
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)
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.