| Author |
 Topic  |
|
|
Villanuev
Constraint Violating Yak Guru
311 Posts |
 Posted - 01/30/2013 : 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. |
Edited by - Villanuev on 01/31/2013 01:22:46
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47171 Posts |
Posted - 01/31/2013 : 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
311 Posts |
Posted - 01/31/2013 : 01:30:37
|
Thank you very much Visakh.
BTW, what is the meaning of "s" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47171 Posts |
Posted - 01/31/2013 : 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
311 Posts |
Posted - 01/31/2013 : 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
|
Edited by - Villanuev on 01/31/2013 03:00:16 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47171 Posts |
Posted - 01/31/2013 : 04:15:48
|
if its hours use "h" instead of "s"
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
time zone offset
