CODEWITHSUNDEEP
sql-server
user3378165
user3378165

Reputation: 6896

Conversion error in SQL Server

I have the following SQL query that gets a date (one parameter of start date and end date separated by a comma) as a parameter and should returns all values between these dates.

(Long query - I'm posting just the relevant part)

Set @SQLQuery = @SQLQuery + 'And (o.Date >= LEFT(''' + @Date + ''', charindex('','',''' + @Date+ ''') - 1)' 
+ ' AND o.Date <= RIGHT(''' + @Date + ''', charindex('','',''' + @Date+ ''') - 1)) '

The format of the date parameter is: 

start date,end date in MM,DD,YYYY format

When the date parameter is for example: 8-5-2015,08-9-2016 it's working perfectly, but when it is for example 8-5-2015,08-11-2016 I'm getting the following error:

Conversion failed when converting date and/or time from character string

I think it's related to the two digits on the days part.

Any idea what can causes that?

Upvotes: 0

Views: 122

Answers (2)

DatabaseCoder
DatabaseCoder

Reputation: 2032

It seems to be problem with logic inside Right function. Please use below logic , i have added length function to find right date correctly.

Set @SQLQuery = @SQLQuery + 'And (o.Date >= LEFT(''' + @Date + ''', charindex('','',''' + @Date+ ''') - 1)' 
+ ' AND o.Date <= RIGHT(''' + @Date + ''', len(''' + @Date+ ''') - charindex('','',''' + @Date+ '''))) '

Upvotes: 3

Hossein Toudehkharman
Hossein Toudehkharman

Reputation: 26

change the query to following type:

Set @SQLQuery = @SQLQuery + 'And (o.Date >= LEFT(''' + @Date + ''', charindex('','',''' + @Date+ ''') - 1)' + ' AND o.Date <= RIGHT(''' + @Date + ''', LEN(''' + @Date+ ''') - charindex('','',''' + @Date+ '''))) '

Upvotes: 1

Related Questions