July 17, 2012 at 2:36 pm
Hello All,
when I executed this query
SELECT COUNT(*) FROM temp_view
where CONVERT(datetime, DATEADD(mm, DATEDIFF(mm, 0, convert(date,left(period_name,CHARINDEX('-',period_name,1)-1)+','+'20'+right(period_name,2))), 0)) >=CONVERT(datetime, DATEADD(mm, DATEDIFF(mm, 0, GETDATE())-12,0))
and PERIOD_NAME<>'Adj-09' , here my period_name is like 'Jan-12'
its working fine but when I execute
SELECT column1,column2 FROM temp_view
where CONVERT(datetime, DATEADD(mm, DATEDIFF(mm, 0, convert(date,left(period_name,CHARINDEX('-',period_name,1)-1)+','+'20'+right(period_name,2))), 0)) >=CONVERT(datetime, DATEADD(mm, DATEDIFF(mm, 0, GETDATE())-12,0))
and PERIOD_NAME<>'Adj-09', its giving me conversion error
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string. I am not getting why is this happening.
Can anyone faced the same kind of issue?
Thanks
July 17, 2012 at 8:55 pm
You have a couple of things going on here. The first is you have some data in period_name that can't be converted to a datetime. This is what is causing the error.
The other thing is HOLY UNSARGABLE BATMAN!!!! What in the world is that monstrous mess of cast, convert, dateadd stuff trying to do? Not trying to sound harsh but I am certain we can find a much easier way to calculate whatever it is you trying to do here. I am going to hazard a guess that whatever datatype this is it isn't a datetime datatype? If you are unsure of what sargable means, a quick google search will reveal that it means "search argument able". That means that what you have will negate any kind of indexing you have because it will have to process the functions for every single row.
If you can post some ddl, sample data and desired output we can clean this up and make this super fast to boot. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply