SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 time zone offset
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 01/30/2013 :  22:40:14  Show Profile  Reply with Quote
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.

Edited by - Villanuev on 01/31/2013 01:22:46

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/31/2013 :  01:19:26  Show Profile  Reply with Quote
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

441 Posts

Posted - 01/31/2013 :  01:30:37  Show Profile  Reply with Quote
Thank you very much Visakh.

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

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/31/2013 :  01:46:37  Show Profile  Reply with Quote
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

441 Posts

Posted - 01/31/2013 :  02:58:12  Show Profile  Reply with Quote
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



Edited by - Villanuev on 01/31/2013 03:00:16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/31/2013 :  04:15:48  Show Profile  Reply with Quote
if its hours use "h" instead of "s"

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000