Post reply

Conversion failed when converting character string to smalldatetime data type.

  • November 12, 2015 at 6:38 pm

    #305146

    Could someone please let me know why I'm having this error? Conversion failed when converting character string to smalldatetime data type.

    declare @BaseYrBegin smalldatetime, @sql nvarchar(max)

    set @BaseYrBegin = '1/1/2014'

    set @sql = 'datepart(year,''' + @BaseYrBegin + '''' + ')'

    exec sys.sp_executesql @sql

  • November 13, 2015 at 1:33 am

    #1839537

    set @sql = 'datepart(year,''' + convert(varchar(10),@BaseYrBegin,103) + '''' + ')'

    Your converting the @sql into a smalldatetime, you need to convert the @BaseYrBegin to a string so that your concatenating strings, the implicit conversion doesn't like it

    Also be weary of using string literals as dates, if you are using string literals always ensure a YYYYMMDD format to prevent any DMY / MDY errors.

  • November 13, 2015 at 4:04 am

    #1839565

    DECLARE

    @BaseYrBegin CHAR(8), -- use the correct data type

    @sql NVARCHAR(100)

    SET @BaseYrBegin = '20140101' -- use an unambiguous date string

    SET @sql = 'SELECT datepart(year,''' + @BaseYrBegin + '''' + ')' -- don't forget the SELECT

    PRINT @sql -- silly to forget this!

    EXEC sys.sp_executesql @sql

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 13, 2015 at 10:30 am

    #1839668

    Thank you so much! You saved me.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply