Conversion issue in 2016 1

[Cpreston]

Hi

Just upgraded to 2016 from 2008 and I am getting this error when running the attached code. I cant find what it is relating to, could someone assist please. Code runs fine in 2008 so presuming something is needed in 2016, Thanks

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

SELECT        dbo.Product.ProductCode, dbo.Product.Description, dbo.ProductStockOption.StockLevelMin, dbo.ProductStockOption.StockLevelMax, SUM(dbo.ProductAnalysis.QuantityUsed) AS QTYUsed, 
                         dbo.Stock.StockAvailable, dbo.Product.udfTopTier, dbo.[148-vwOnWorksOrder].m3 AS [m3 on WO], dbo.[148-vwOnWOSchedule].ScheduleNumber, 
                         dbo.[148-vwAverageDailyUsageMDF_3Month Version].WorkingDaysL3
FROM            dbo.ProductAnalysis INNER JOIN
                         dbo.Product ON dbo.ProductAnalysis.ProductID = dbo.Product.ProductID INNER JOIN
                         dbo.ProductStockOption ON dbo.Product.ProductID = dbo.ProductStockOption.ProductID INNER JOIN
                         dbo.Stock ON dbo.ProductStockOption.ProductID = dbo.Stock.ProductID AND dbo.ProductStockOption.BranchID = dbo.Stock.BranchID INNER JOIN
                         dbo.[148-vwOnWorksOrder] ON dbo.ProductAnalysis.ProductID = dbo.[148-vwOnWorksOrder].ProductID INNER JOIN
                         dbo.[148-vwAverageDailyUsageMDF_3Month Version] ON dbo.ProductAnalysis.ProductID = dbo.[148-vwAverageDailyUsageMDF_3Month Version].ProductID LEFT OUTER JOIN
                         dbo.[148-vwOnWOSchedule] ON dbo.Product.ProductID = dbo.[148-vwOnWOSchedule].ProductID
WHERE        (dbo.ProductAnalysis.BranchID IN (1, 9)) AND (dbo.ProductAnalysis.CalendarYear = DATEPART(year, DATEADD(month, - 1, GETDATE()))) AND (dbo.ProductAnalysis.CalendarMonth = DATEPART(month, 
                         DATEADD(month, - 1, GETDATE()))) OR
                         (dbo.ProductAnalysis.CalendarYear = DATEPART(year, DATEADD(month, - 2, GETDATE()))) AND (dbo.ProductAnalysis.CalendarMonth = DATEPART(month, DATEADD(month, - 2, GETDATE()))) OR
                         (dbo.ProductAnalysis.CalendarYear = DATEPART(year, DATEADD(month, - 3, GETDATE()))) AND (dbo.ProductAnalysis.CalendarMonth = DATEPART(month, DATEADD(month, - 3, GETDATE())))
GROUP BY dbo.Product.ProductCode, dbo.Product.Description, dbo.ProductStockOption.StockLevelMin, dbo.ProductStockOption.StockLevelMax, dbo.Stock.StockAvailable, dbo.Product.udfTopTier, 
                         dbo.[148-vwOnWorksOrder].m3, dbo.[148-vwOnWOSchedule].ScheduleNumber, dbo.[148-vwAverageDailyUsageMDF_3Month Version].WorkingDaysL3
HAVING        (dbo.ProductStockOption.StockLevelMin > 0) AND (dbo.ProductStockOption.StockLevelMax > 0)

 

[johnherman]

It's not immediately obvious to me. You can try removing clauses to try to find out which one(s) are not being accepted. For instance, remove the HAVING clause, then remove the GROUP BY clause, then remove parts of the WHERE clause, etc.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Cpreston]

Appreciate the4 reply. I have tried every combination going but cannot find what it is trying to convert. Even took out all dateparts and dateadds but still thinks it is converting some date time

Thanks

 

[SkipVought]

This can often be tedious, time consuming work, but you must deconstruct your SQL until you find the culprit.

Ball’s in your court.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Cpreston]

Let me run this by you

Old server as dateformat mdy
new server as dateformat dmy

Could this cause the issue?

 

[gmmastros]

Could this cause the issue?

You can test this easily by adding a set dateformat statement before the code. Ex:

SET DATEFORMAT MDY
-- Put your code here

Also... looks like you are using several views here. Are any of them doing a conversion from string to DateTime?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Hi

Brilliant putting SET DATEFORMAT MDY at the start of the code works fine if you run it from query window. I have tried to add it to the start of a view script but it wont save.

It complains it cannot be represented as a grid pane, which is fine. I click continue and the code runs ok. When I close and click Yes to save it it says it is incorrect sysntax near the word SET.

Could you advise how to get the syntax so it saves within the view. Many thanks

 

[Cpreston]

Hi

Thnkas for the reply's we have sorted it now. We changed the date format on the user settings and this is now working.

 

[gmmastros]

I'm glad you got it sorted.

Honestly though, I worry about your data. The only reason this code would help is because you must have dates stored in a varchar column. In my opinion, this is a recipe for disaster. I understand about priorities and big changes, but I do encourage you to address this issue eventually. The sooner the better. Specifically, I encourage you to change the data type for the column to DateTime (or perhaps just Date) so that you won't need to worry about conversion problems. I suspect your queries would also perform better.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

on top of what George said which is what should be the correct solution

I would bet that somewhere on those views of yours you are doing a cast/convert of such a char field containing a date - while bad on its own and because it worked when you changed the date format for the user, that also means that you are doing such convert without specifying a format.

As a standard, and if there is no way to change that field to be the correct data type then at lease code the exact format that the data should be in, and enforce the data going into that field to be on that exact format.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886