Date nvarchar to datetime conversion problem

• Ryan-487455

  Old Hand

  Points: 379

  More actions

  February 5, 2008 at 10:18 am

  #186409

  Hello,

  I have a sql string which incorporates a passed in nvarchar(30) variable with the following format and name:

  DECLARE @the_date_string NVARCHAR(30), @the_date DATETIME, @sql_string NVARCHAR(100)

  SET @the_date_string = '01/31/2008 9:33 PM'

  I am attempting convert this string to a datetime format, then place the variable into sql string to be fired by the EXECUTE command.

  @the_date = CONVERT(datetime, @the_date_string)

  Here is an example of what works withOut using the EXECUTE command:

  SELECT @the_date AS theDate

  Here I attempt to place the above SELECT statement into a string to be fired by the EXECUTE command with no luck:

  SET @sql_string = 'SELECT ' + @the_date + ' AS theDate'

  EXEC (@sql_string)

  This above code throws an error as follows:

  Msg 241, Level 16, State 1, Line 293

  Conversion failed when converting datetime from character string.

  I believe this pertains to an issue of conflicting types when placing a datetime type into a nvarchar type of the sql_string variable,

  What type of solution(s) is/are there for this problem?

  Thanks for any assistance,

  Ryan

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  February 5, 2008 at 10:24 am

  #775229

  Dates need to be enclosed with single quotes.

  DECLARE @the_date_string NVARCHAR(30),

  @the_date DATETIME,

  @sql_string NVARCHAR(100)

  SELECT@the_date_string = '01/31/2008 9:33 PM',

  @the_date = CONVERT(datetime, @the_date_string, 101)

  SET@sql_string = 'SELECT ''' + @the_date_string + ''' AS theDate'

  EXEC(@sql_string)

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 5, 2008 at 10:29 am

  #775232

  The problem is not with the conversion from @the_date_string to @the_date. SQL is try to convert the 'SELECT ' and the ' AS theDate' strings to a datetime value where you are trying to create the @sql_string to be executed with the EXEC statement.

  You need to convert @the_date back to a string or use @the_date_string.

  😎

• Ryan-487455

  Old Hand

  Points: 379

  More actions

  February 5, 2008 at 11:26 am

  #775260

  Wow!

  I was looking at this from a web designer's perspective about quotations, because I tried all combinations of double and single quotations except the three successive single quote wrap, you mentioned.

  I was thinking that there was no way something like that was going to work!

  Thank you all so much for your expertise! You guys rock!

  Ryan