Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 time zone offset

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-01-30 : 22:40:14
Hi Guys,

Need your help. I have a date and time parameter on my SSRS report.
my problem is how could i place the timezoneoffset=8 and alos i got an error. see below. thanks.

sample:
DECLARE @fromDateTime as datetime, @toDateTime as datetime,
@sdate varchar(10), @stime varchar(15), @edate varchar(10), eetime varchar(15), @timezoneoffset int

set @sdate = '1/1/2013'
set @stime = '12:00 AM'
set @edate = '1/30/2013'
set @etime = '12:00 AM'
Set @timezoneoffset=0


in my SSRS dataset properties in parameter expression
here is what i do to combine the date and time:

@FromDateTime
=Parameters!Sdate.Value &" "&Parameters!Stime.Value

@ToDatetime
=Parameters!edate.Value &" "&Parameters!etime.Value


My Query:
Select * from MyTable
Where ASUFROMDATE @FromDateTime
and @ToDatetime


Upon Running my Query i got also an Error:

Conversion failed when converting date and /or time from character string.
Thank you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 01:19:26
you need to use dateadd() function for that

do something like

=DateAdd("s",val(Parameters!timezoneoffset.value),CDATE(Parameters!Sdate.Value &" "&Parameters!Stime.Value))

i hope date values in Sdate are of proper format

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-01-31 : 01:30:37
Thank you very much Visakh.

BTW, what is the meaning of "s"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 01:46:37
it was abbreviation for second

i hope offset contains unit as seconds

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-01-31 : 02:58:12
Hi Visakh,

Upon running your code in my script it gives me different date and time:
The timeZoneoffset is 8 HRs ( US time)

=DateAdd("s",val(Parameters!timezoneOffset.Value),CDATE(Parameters!sDate.Value &" "&Parameters!sTime.Value))
FromDateTime = 1/1/2013 12:00:08 AM

=DateAdd("s",val(Parameters!timezoneOffset.Value),CDATE(Parameters!eDate.Value &" "&Parameters!eTime.Value))
ToDateTime = 1/30/2013 12:00:08 AM


Correct result should be:

fromdatetome----------------todatetime
2012-12-31 16:00:00.000-----2013-01-29 16:00:00.000


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 04:15:48
if its hours use "h" instead of "s"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -