0

I'm retrieving data from a view, and one of the columns I'm using is nvarchar(50), but is only ever N'True' or N'False', depending on the operation of a related date column in this parent view.

The following code retrieves the record ID and the column I'm looking for, YTD:

SELECT Enquiry_Number, YTD 
FROM dbo.vw_SalesPO_YTD

Output:

ENQ-001 True
ENQ-002 False
ENQ-003 True

However, I'm unable to filter my results using this YTD column for some reason. If I attempt to do this:

SELECT Enquiry_Number, YTD
FROM dbo.vw_SalesPO_YTD
WHERE YTD = N'True'

Then it fails with the following error:

Arithmetic overflow error converting expression to data type datetime.

Which I don't understand because there are no datetime expressions in play in this query. Yes the True or False was determined by comparing datetimes in the parent view, but I don't understand how that might have trickled down to this subquery. Attempting the same thing in the parent view yields the same error - I'm demonstrating it this way for simplicity's sake.

However, performing a similar operation in the SELECT portion of the query works without issues:

SELECT 
    Enquiry_Number,
    YTD,
    CASE 
       WHEN YTD = N'True' THEN 1 ELSE 0 
    END As C
FROM
    dbo.vw_SalesPO_YTD

Output:

ENQ-001 True 1
ENQ-002 False 0
ENQ-003 True 1

However, these 1's and 0's inherit the same flaw, where I can't use them in a WHERE clause without getting this datetime error.

I've been searching hard and am not sure how to identify the core issue. I've been reading things about Collations and type precedence, but can't understand why this behaviour is happening.

When I've checked YTD in INFORMATION_SCHEMA.COLUMNS, it confirms that this column is no different from other columns in my table: YTD is nvarchar(50), using the Latin1_General_CI_AS collation.

Related question: SQL Server Arithmetic overflow error converting expression to data type datetime

The Source of the Problem

This issue is still unsolved, but if you wish to reproduce it, this code from the parent view must be generating this issue:

CASE WHEN 
Award_Date <= DATEFROMPARTS(FinancialYear - 1, 11, 1) + GETDATE() - DATEADD(year, DATEDIFF(month, '20161101', GETDATE()) / 12, '20161101')
THEN N'True'
ELSE N'False'
END

Yes this looks overly complicated. We're checking the Award_Date against its associated FinancialYear, which runs from November 1st to October 31st. Each record already knows which FinancialYear it's in. The ultimate aim is to compare TODAY's position (2016-11-30) against TODAY last year (2015-11-30), and TODAY the year before (2014-11-30), etc.

So the code takes today's date and combines it with the FinancialYear for the associated record, and spits out whether the record had occurred between the start of its financial year and the today of the same year. And it's doing this successfully, but then I can't do anything with the N'True' or N'False' it's producing.

Improve this question
12
  • And what do you get if in your query in move the WHERE condition inside the inner select (i.e. (SELECT Enquiry_Number, YTD FROM dbo.vw_SalesPO_YTD WHERE YTD = N'True')) also, note that you are starting with SELECT * FROM... while it should be SELECT A.* FROM... (not sure this is related to your issue though). Commented Nov 30, 2016 at 12:36
  • Sanity check: does a SELECT * FRMO dbo.vw_SalesPO_YTD successfully complete? (Make sure you force all the rows to come back when testing.) Commented Nov 30, 2016 at 12:51
  • @FDavidov - thanks for that, I'd simplified the problem for putting on SE, but hadn't noticed I could cut it down to just the outer query! Have simplified in the Question, but it's still failing. Matt Gibson - yes, selecting all works fine, and I can filter (WHERE) by any column except YTD. Commented Nov 30, 2016 at 12:53
  • Did you try WHERE YTD = 'True'? (without the N) Commented Nov 30, 2016 at 12:57
  • @FDavidov - yes, that also fails with the same error, as does explicitly CONVERT'ing both sides of the WHERE clause to nvarchar(50) Commented Nov 30, 2016 at 12:59

5 Answers 5

Reset to default
1

I do not know what the type of the source YTD is.

Try using the following:

SELECT Enquiry_Number, [YTD] FROM (
    SELECT Enquiry_Number, CONVERT(nvarchar(10),YTD) AS [YTD] FROM dbo.vw_SalesPO_YTD
) AS A
WHERE A.YTD = N'True'

10 is just a thump suck value. It will cut of any part of the field longer that 10. It depends on your actual field size.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

SELECT * FROM (SELECT Enquiry_Number, YTD FROM dbo.vw_SalesPO_YTD) AS A WHERE cast(A.YTD as varchar) = 'True'

Improve this answer

3 Comments

Convert as varchar. Don't into nvarchar
Don't convert as nvarchar
Till now, you didn't get any answer for this question?
1

I used the following as an example:

DECLARE @Data TABLE
(
    Enquiry_Number nvarchar(10),
    YTD nvarchar(50)
)

INSERT INTO @Data(Enquiry_Number, YTD)
SELECT N'ENQ-001', N'True' UNION
SELECT N'ENQ-002', N'False' UNION
SELECT N'ENQ-003', N'True' 


SELECT Enquiry_Number, [YTD] FROM (
    SELECT Enquiry_Number, CONVERT(nvarchar(10),YTD) AS [YTD] FROM @Data
) AS A
WHERE A.YTD = N'True'

Result:

ENQ-001 True
ENQ-003 True

There must be results in the YTD field that causes to return it as a datetime type.

Try a query like:

SELECT * FROM dbo.vw_SalesPO_YTD WHERE ISDATE(YTD)= 1

Updated Question: Try: 

ISNULL(CASE WHEN  Award_Date <= DATEFROMPARTS(FinancialYear - 1, 11, 1) + GETDATE() - DATEADD(year, DATEDIFF(month, '20161101', GETDATE()) / 12, '20161101') THEN N'True' ELSE N'False' END, 'false')
Improve this answer

2 Comments

Thanks @Danie - your code runs successfully and produces the same unfiltered results as my original code, but I still get the same error when trying to filter by the column.
And for the record, the ISNULL's alternative result is never triggered in the results - all the records are showing the True or False, not the false the ISNULL would produce.
1

Try this:- Don't add n before string

SELECT * FROM (SELECT Enquiry_Number, YTD FROM dbo.vw_SalesPO_YTD) AS A WHERE A.YTD = 'True'

Improve this answer

3 Comments

Can you give me your date calculation in codings. That is the source of this problem
cast(cast(@yr_mnth_dt as char(8)) as datetime)
convert your date like this and then calculate.If it is not solve give me date calculation code
0

This is a successful workaround, not a solution per se or explanation of the core issue.

There's obviously an issue with the data coming out of the view, which will not allow YTD column's results to be operated on in a WHERE clause, and yet they can be operated on by the time the query reaches its SELECT phase.

I've created a new table which explicitly defines the YTD column as nvarchar(50), and then inserted all the records from my view into this table, which has resolved the issue. The records can then be sorted and filtered by YTD as they are supposed to.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.