conversion failed when converting datetime from character string

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)


     year(hh.half_hour_dt) as 'Year',
    month(hh.half_hour_dt) as 'Month',
    day(hh.half_hour_dt) as 'Day',
    a ,
    b ,
    d ,
    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
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) ,
    b, c,
order by 

Can you plz tell me what's wrong here?

Thanks alot, SQL_user
more ▼

asked Dec 27, 2011 at 01:54 AM in Default

SQL USER gravatar image

12 5 6 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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


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

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

answered Dec 27, 2011 at 03:46 AM

Usman Butt gravatar image

Usman Butt
13.9k 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, 2011 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, 2011 at 04:07 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 27, 2011 at 01:54 AM

Seen: 2984 times

Last Updated: Dec 27, 2011 at 03:34 AM