Date nvarchar to datetime conversion problem

Question

Post reply

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

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

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

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 1

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: 442447 More actions · Answer 2

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 · Answer 3

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