time zone offset - SQL Server Forums

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/

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"

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/

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

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources