Weird converting datetime error 1

[christer99]

Getting Syntax error converting datetime from character string when doing: SET @SQL=' CONVERT(DateTime,"'+@v_excldate +'") AS DD'

@v_excldate is set to 2006-01-01 00:00:00.000

full code below. Appreciate any help.

declare @SQL VARCHAR(8000)
DECLARE @v_excldate DATETIME

set @v_excldate= (SELECT DATECUT FROM dbo.ProspImportConf)
if (isdate(@v_excldate)=0) set @v_excldate='1900-01-01'


SELECT @v_excldate

SET @SQL=' CONVERT(DateTime,"'+@v_excldate +'") AS DD'
SELECT @SQL

 

[onpnt]

I'm a bit confused because what you have shown above compiles down to converting a datetime to a datetime ??

To fix your problem however you must cast the datetime variable as a string to use it as a string.

You should also be more careful at your query to set the variable sense you are not restricting the return to one value

e.g.

[COLOR=blue]Declare[/color]   @SQL [COLOR=blue]VARCHAR[/color](8000)
[COLOR=blue]Declare[/color] @v_excldate [COLOR=#FF00FF]DateTime[/color]
 
[COLOR=blue]set[/color] @v_excldate= [COLOR=red]'2006-01-01 00:00:00.000'[/color]
[COLOR=blue]If[/color] ([COLOR=#FF00FF]isdate[/color](@v_excldate)=0) 
 [COLOR=blue]Begin[/color]
	[COLOR=blue]set[/color] @v_excldate=[COLOR=red]'1900-01-01'[/color]
 [COLOR=blue]End[/color]

[COLOR=blue]Select[/color] @v_excldate

[COLOR=blue]SET[/color] @SQL=[COLOR=red]'CONVERT(DateTime,'''[/color]+ [COLOR=#FF00FF]Cast[/color](@v_excldate [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]) +[COLOR=red]''') AS DD'[/color]
[COLOR=blue]Select[/color] @SQL

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}