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.
| Author |
Topic |
|
maihabib
Starting Member
22 Posts |
 Posted - 2011-12-27 : 06:07:53
|
| Hi there,I have an SSIS package and I have this SQL query in a variable.@[User::start_date] and @[User::end_date] are two variables in the package and I can get them successfully but when it runs this query I get this error: "conversion failed when converting datetime from character string"declare @ds datetime, @de datetimeset @ds='"+(DT_WSTR,30)@[User::start_date]+"' set @de='"+(DT_WSTR,30)@[User::end_date]+"'set @ds=CAST(CAST(YEAR(@ds) AS NVARCHAR(4)) + '/' +CAST(MONTH(@ds) AS NVARCHAR(2))+ '/' + CAST(DAY(@ds) AS NVARCHAR(2))+ ' 00:00:00' AS DATETIME) set @de=CAST(CAST(YEAR(@de) AS NVARCHAR(4)) + '/' +CAST(MONTH(@de) AS NVARCHAR(2)) + '/' + CAST(DAY(@de) AS NVARCHAR(2))+ ' 23:59:59' AS DATETIME)select year(hh.half_hour_dt) as 'Year',month(hh.half_hour_dt) as 'Month',day(hh.half_hour_dt) as 'Day',a ,b ,d ,efrom table t inner join hh with (nolock) on t.lhkey = hh.lhkey inner join cr with (nolock) on t.ckey = cr.ckey inner join lob with (nolock) on lkey = cr.lkey inner join ac with (nolock) on ac.akey = lob.akey inner join si with (nolock) on si.skey = cr.skey inner join bc with (nolock) on bc.bkey = fact.bkey where a = '"+@[User::accc]+"' and h between @ds and @de and exists ( select 1 from table2 where l_key = ls_key and si.site_key = lsbc.site_key and bc.bill_code_key = lsbc.bill_code_key and hh.half_hour_dt between lsbc.row_active_date and lsbc.row_inactive_date ) group by year(hh.half_hour_dt) , month(hh.half_hour_dt) , day(hh.half_hour_dt) , a, b, c, d order by a,b,cCan you plz tell me what's wrong here?Thanks alot, SQL_user |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
maihabib
Starting Member
22 Posts |
Posted - 2011-12-27 : 06:49:57
|
They are variables in SSIS and I get them from a table, their format is datetime '2011-11-01 00:00:00.000'plz help,Thanksquote:
Originally posted by visakh16
where are you getting start_date and end_date variable values from? If you're making them in YYYYMMDD format there itself it will not throw errorsee why you're getting this error belowhttp://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-27 : 06:58:05
|
| then make the expression like below(DT_WSTR,10) (DT_DBDATE) @[System::start_date]etcrather than what you've currently------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
maihabib
Starting Member
22 Posts |
Posted - 2011-12-27 : 07:45:19
|
Thanks thanks thanks. it worked :)quote:
Originally posted by visakh16
then make the expression like below(DT_WSTR,10) (DT_DBDATE) @[System::start_date]etcrather than what you've currently------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-27 : 08:19:15
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
conversion failed when converting datetime from ch