May 16, 2015 at 2:04 pm
I have a query that worked perfectly fine until I added a Row_Number column in with a Date column specified in the order clause.
The error I get is:
Msg 241, Level 16, State 1, Line 27
Conversion failed when converting date and/or time from character string.
I assumed it was because I had both a date and varchar column in the case statement. But it only doesn't work if the @SortOrder is "DocumentName" (varchar). But if I have @SortOrder as "[Year]" (varchar) or "ExpirationDate" (DateTime), it works fine.
Why would the first one cause a problem?
DECLARE@SortOrder VARCHAR(100),
@SortDirection VARCHAR(100)
SELECT @SortDirection = 'ASC'
SELECT @SortOrder = 'DocumentName'
--SELECT @SortOrder = '[Year]'
--SELECT @SortDirection = 'ExpirationDate'
DROP TABLE #Test
CREATE TABLE #TEST
(
DocumentName VARCHAR(100),
[Year] VARCHAR (4),
ExpirationDate DateTime
)
INSERT #Test (DocumentName,[Year],ExpirationDate)
VALUES ('Document 1', '2014', '02/01/2015')
INSERT #Test (DocumentName,[Year],ExpirationDate)
VALUES ('Document 2', '2011', '05/09/2013')
INSERT #Test (DocumentName,[Year],ExpirationDate)
VALUES ('Document 1', '2013', '11/01/2014')
INSERT #Test (DocumentName,[Year],ExpirationDate)
VALUES ('Document 1', '2012', '11/12/2012')
SELECT DocumentName,
[Year],
ExpirationDate,
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @SortDirection = 'ASC' THEN
CASE @SortOrder
WHEN 'DocumentName' THEN DocumentName
WHEN 'Year' THEN [Year]
WHEN 'ExpirationDate' THEN ExpirationDate
END
END ASC)
FROM #TEST
Thanks,
Tom
May 16, 2015 at 2:13 pm
tshad (5/16/2015)
I assumed it was because I had both a date and varchar column in the case statement. But it only doesn't work if the @SortOrder is "DocumentName" (varchar). But if I have @SortOrder as "[Year]" (varchar) or "ExpirationDate" (DateTime), it works fine.Why would the first one cause a problem?
Because '2012' can be implicitly converted to datetime, but 'Document 1' can't. It's about precedence of data types and if you have varchar and datetime and SQL needs to convert to one data type (as it does in the CASE), it will try to convert varchar to datetime and 'Document 1' is not a valid datetime data type.
You need to explicitly cast ExpirationDate to varchar in the CASE, make sure you use a format code that gives you a yyyymmdd order to ensure it sorts correctly
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2015 at 2:19 pm
Further on Gail's answer
😎
DECLARE@SortOrder VARCHAR(100),
@SortDirection VARCHAR(100)
SELECT @SortDirection = 'ASC'
SELECT @SortOrder = 'ExpirationDate' --'DocumentName' --NULL --'DocumentName'
--SELECT @SortOrder = '[Year]'
--SELECT @SortDirection = 'ExpirationDate'
IF OBJECT_ID(N'tempdb..#TEST') IS NOT NULL DROP TABLE #TEST;
CREATE TABLE #TEST
(
DocumentName VARCHAR(100),
[Year] VARCHAR (4),
ExpirationDate DateTime
)
INSERT #Test (DocumentName,[Year],ExpirationDate)
VALUES ('Document 1', '2014', '02/01/2015')
INSERT #Test (DocumentName,[Year],ExpirationDate)
VALUES ('Document 2', '2011', '05/09/2013')
INSERT #Test (DocumentName,[Year],ExpirationDate)
VALUES ('Document 1', '2013', '11/01/2014')
INSERT #Test (DocumentName,[Year],ExpirationDate)
VALUES ('Document 1', '2012', '11/12/2012')
SELECT DocumentName,
[Year],
ExpirationDate,
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @SortDirection = 'ASC' THEN
CASE
WHEN @SortOrder = 'Year' THEN CONVERT(VARCHAR(4),[Year],0)
WHEN @SortOrder = 'ExpirationDate' THEN CONVERT(VARCHAR(32),ExpirationDate,126)
WHEN @SortOrder = 'DocumentName' THEN DocumentName
END
END ASC)
FROM #TEST
May 16, 2015 at 2:56 pm
That makes sense and works fine now.
Thanks
May 16, 2015 at 2:59 pm
In your solution, was there a reason you changed the order of the WHEN clauses or does it matter.
It didn't seem to in my solution after I added the converts.
Thanks
May 16, 2015 at 3:13 pm
tshad (5/16/2015)
In your solution, was there a reason you changed the order of the WHEN clauses or does it matter.It didn't seem to in my solution after I added the converts.
Thanks
No reason other than being to lazy to reorder the code, as Gail so elegantly stated, the problem lies in the data type precedence, hence not in the order of appearance.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply