Error converting data type varchar to date in SQL Server 2008 R2

Question

I have the date to check as shown below:

Input Date:

 17-09-2014

For which I am converting in my dynamic script:

Attempt #1:

 CAST((convert(date,@FDate, 105)) AS nvarchar(50))

Error:

Error converting data type varchar to date.

Attempt #2:

 convert(date, @FDate, 105)

Error:

The data types nvarchar and date are incompatible in the add operator.

Attempt #3:

 cast(@FDate as varchar(50))

Error:

Error converting data type varchar to date.

One whole attempt, taken from the sqlfiddle.com/#!3/d41d8/38976 of the comments:

DECLARE @querys NVARCHAR(max)
DECLARE @Date DATE
SET @Date = '17-09-2014'

SET @querys = 'SELECT' + CAST((convert(date,@Date, 105)) AS nvarchar(50)) + ''
EXEC(@querys)

Answer 1

Try

convert(Datetime, @FDate,105)

I tried following script and it worked well for SQL Server 2005 and SQL Server 2012:

Declare @FDate varchar(100);
set @FDate='17-09-2014';
Select convert(Varchar(50), convert(Datetime, @FDate,105) ,105)

Verified your fiddle script, just had small change and it worked as expected.

Here is new script that I tested on fiddle:

DECLARE @qs VARCHAR(max)
Declare @FsDate varchar(100)
set @FsDate = '17-09-2014'

SET @qs = 'Select  convert(Varchar(50), convert(Datetime, '''+@FsDate+''',105) ,105) '

EXEC(@qs)

Answer 2

There seems to be some confusion with Convert. Comparing your fiddels from the comments and your shown Attempts. Your fiddels are showing DECLARE @Date DATE; The first argument is the targettype (Attempt #1) since your @FDate is already of type DATE convert(date,@FDate, 105) will lead to a conversion from DATE to DATE, your outer cast to nvarchar does not seem to work due to your locales. (Attempt #2) is shown incomplete since the shown part convert(date, @FDate, 105) does work, even it won't change anything (conversion from DATE to DATE)
(Attempt #3) does not seem to work due to your locales.

Your shown fiddle:

DECLARE @querys NVARCHAR(max)
DECLARE @Date DATE
SET @Date = '17-09-2014'
SET @querys = 'SELECT' + CAST((convert(date,@Date, 105)) AS nvarchar(50)) + ''
EXEC(@querys) 

already is failing here SET @Date = '17-09-2014', a save way independed from locales would be to use the format YYYYMMDD SET @Date = '20140917'. Since you are trying to buid a varchar your targettype for CONVERT would by VARCHAR not DATE and you wuold have to add quotation marks, a simple PRINT @querys or SELECT @querys would show what you are trying to execute.

Taken from your fiddle, you are trying to convert a Date to a varchar and then add it to a dynamic SQL which you want to execute, so one way to go would be:

DECLARE @querys NVARCHAR(max)
DECLARE @Date DATE

SET @Date = '20140917'
-- get it as varchar
--SET @querys = ' SELECT ''' + convert(varchar(20),@Date,105) + ''''

--get it as date
SET @querys = ' SELECT convert(date,''' + convert(varchar(20),@Date,105) + ''',105)'

EXEC(@querys)

Answer 3

Try like this!

declare @a varchar(50)
set @a='17-09-2014'


select cast( right(@a,4)+'-'+SUBSTRING(@a,4,2)+'-'+LEFT(@a,2) as date)

FIDDLE DEMO