using date variable in dynamic sql statement

Posted on 2008-11-19
Last Modified: 2012-05-05
hi experts,

this is driving me crazy.  how can i use a date field in a dynamic sql statement?  When I try to run the code below, I get:

Conversion failed when converting datetime from character string.

declare @sql_stmt            nvarchar(2000)
declare      @charge_date      datetime

set @charge_date =  '11/21/2008'

set @sql_stmt = 'select distinct id '
set @sql_stmt = @sql_stmt + 'from table'
set @sql_stmt = @sql_stmt + 'where move_date < '
set @sql_stmt = @sql_stmt + @charge_date
set @sql_stmt = @sql_stmt + ' '

Question by:thewayne73
    LVL 60

    Expert Comment

    declare @sql_stmt            nvarchar(2000)
    declare      @charge_date      datetime

    set @charge_date =  '11/21/2008'

    set @sql_stmt = 'select distinct id '
    set @sql_stmt = @sql_stmt + 'from table'
    set @sql_stmt = @sql_stmt + 'where move_date < '
    set @sql_stmt = @sql_stmt + convert(varchar(10), @charge_date, 101)
    set @sql_stmt = @sql_stmt + ' '
    LVL 142

    Accepted Solution

    you should consider to avoid dynamic sql.

    now, you have 2 solution ( apart from the non-dynamic sql):
    method 1 (not recommended): 
    set @sql_stmt = 'select distinct id '
    set @sql_stmt = @sql_stmt + 'from table'
    set @sql_stmt = @sql_stmt + 'where move_date < CONVERT(datetime, '''
    set @sql_stmt = @sql_stmt + CONVERT(varchar(10), @charge_date, 120)
    set @sql_stmt = @sql_stmt + ''', 120) ' 
    method 2 (much easier): 
    set @sql_stmt = 'select distinct id '
    set @sql_stmt = @sql_stmt + 'from table'
    set @sql_stmt = @sql_stmt + 'where move_date < @charge_date ' 
    exec sp_executesql @sql_stmt, N'@charge_date datetime', @charge_date

    Open in new window


    Author Closing Comment


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now