sql - conversion failed when converting date from character string

Question

Hy,

I have a problem and I can't figure it out...

    declare @start_date datetime, @date_1m datetime, @date_1m_end datetime

    set @start_date = GETDATE()
    set @date_1m = DATEADD(MONTH,-1,DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date),0))
    set @date_1m_end = DATEADD(SS, -1, DATEADD(MONTH, 1, @date_1m))

    set @sql_exec = 'insert into #temp select ... where a.date between ' + @date_1m + ' and ' + @date_1m_end + ''
exec(@sql_exec)

and it gives me the following error :

Conversion failed when converting date and/or time from character string.

Why and how to solve this because it's getting frustrating ..

PS: I need to run exec() because this stored procedure will run every month and it creates database for that month

Thanks you very much

Nikola Markovinović · Accepted Answer · 2012-04-05 08:01:32Z

1

TSql is trying to convert all your text to datetime. You need to convert dates to strings before concationation, and convert them back to dates in query.

set @sql_exec = 'insert into #temp select ... where a.date between ' 
    + 'convert(datetime, ''' 
    + convert(varchar(10), @date_1m, 104) 
    + ''', 104)'
    + ' and '
    + 'convert(datetime, ''' 
    + convert (varchar(10), @date_1m_end, 104) 
    + ''', 104)'

answered Apr 5, 2012 at 8:01

Nikola Markovinović

19.4k5 gold badges48 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql - conversion failed when converting date from character string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related