conversion failed when converting datetime from character string

Question

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 datetime

set @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 ,
    e 
from 
    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,c

Can you plz tell me what's wrong here?

Thanks alot, SQL_user

Answer 1

0

You need to use SUBSTRING function to convert it to desired format before assigning and ISDATE() function before converting to datetime. Moreover, It will purely be dependent upon the input. If I assume the input is in YYYYMMDD format then it could be

DECLARE @ds DATETIME, @de DATETIME, @StartDate VARCHAR(25), @EndDate VARCHAR(25)

set @StartDate='"+(DT_WSTR,30)@[User::start_date]+"'
set @EndDate ='"+(DT_WSTR,30)@[User::end_date]+"'

SET @StartDate = SUBSTRING(@StartDate,1,4) + '/' + SUBSTRING(@StartDate,5,2) + '/'+ SUBSTRING(@StartDate,7,2) + ' 00:00:00'

SET @EndDate = SUBSTRING(@EndDate,1,4) + '/' + SUBSTRING(@EndDate,5,2) + '/'+ SUBSTRING(@EndDate,7,2) + ' 23:59:59.997' -- ADDED missing .997 seconds

IF ISDATE(@StartDate) = 1 AND ISDATE(@EndDate) = 1


SELECT @ds = CAST(@StartDate AS DATETIME), @de = CAST(@EndDate AS DATETIME)

If you can tell us what kind of input you are expecting then we may lead to better approach.

answered Dec 27 '11 at 03:46 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

When I tried your suggestion I get this error: "Arithmetic overflow error converting expression to data type datetime."

I think the issue is here, because when I execute it on management studio I get the error: Conversion failed when converting datetime from character string. and it points to this line set @ds='"+(DT_WSTR,30)@[User::start_date]+"' set @de='"+(DT_WSTR,30)@[User::end_date]+"'

Hope you can help Thanks,

Dec 27 '11 at 03:56 AM SQL USER

you should add ISDATE() function to validate before assigning. There is no point in assiging it before and then change it according to your LANGUAGE settings :). I have changed it a bit. But If you could let us know the details, may be we could come up with a better approach

Dec 27 '11 at 04:07 AM Usman Butt

(comments are locked)

conversion failed when converting datetime from character string

Follow this question

Related Questions