• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 743
  • Last Modified:

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()))))
0
pgmtkl
Asked:
pgmtkl
  • 4
  • 3
1 Solution
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
dbaSQLCommented:
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now