Okay, here is a repro:
USE tempdb;
GO
-- create one table with padding off:
SET ANSI_PADDING OFF;
GO
CREATE TABLE dbo.ap_off(a CHAR(1),
CONSTRAINT ck_apOff CHECK (a IN ('N','Y')));
GO
-- and other with padding on:
SET ANSI_PADDING ON;
GO
CREATE TABLE dbo.ap_on(a CHAR(1),
CONSTRAINT ck_apOn CHECK (a IN ('N','Y')));
GO
-- now, let's test queries
-- with padding on or off
SET ANSI_PADDING OFF;
GO
-- implicit conversion:
DECLARE @off CHAR(1) = (SELECT a FROM dbo.ap_off);
-- implicit conversion:
DECLARE @on CHAR(1) = (SELECT a FROM dbo.ap_on);
SET ANSI_PADDING ON;
GO
-- implicit conversion:
DECLARE @off CHAR(1) = (SELECT a FROM dbo.ap_off);
-- NO implicit conversion:
DECLARE @on CHAR(1) = (SELECT a FROM dbo.ap_on);
GO
DROP TABLE dbo.ap_off, dbo.ap_on;
Here's the same version of the plan for the first three queries:
And the fourth, missing the compute scalar and any implicit conversion:
So, as I suggested above, this doesn't have anything to do with alias types, but rather the fact that ANSI_PADDING
is problematic in a whole bunch of ways (here's a timely article, a Stack Overflow question, and Microsoft's own documentation from SQL Server 2005 telling you to stop using it).
My suggested approach is to build new versions of any table with these non-ANSI_PADDING
columns (this time with ANSI_PADDING ON
of course), migrate the data, drop the old tables, and rename the new tables. Or do the same type of thing with just the individual columns (in either case it's going to be a complicated and disruptive thing that you'll likely want to do during a maintenance window).
You can identify the affected tables with:
SELECT s.name, t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE t.is_ms_shipped = 0
AND EXISTS (SELECT 1 FROM sys.columns
WHERE [object_id] = t.[object_id]
AND is_ansi_padded = 0);
I would do this with one table or columns first, to see if it's worth it. See, in addition to eliminating the implicit conversions (which you think might cause performance issues), you should also demonstrate that performance will actually be different without them - and different enough to justify the effort, risk, and potential downtime. Otherwise, is it worth doing? We can't answer that, only you and your stakeholders can.
Since you are not shared the sample data of the Gradd19
value, I suspect the date value is in the yyyy-dd-mm
format, so when trying to convert (month value more than 12) it cause the out-of-range value error.
For sample, if the date format in yyyy-mm-dd
, it will execute properly:
DECLARE @FromDate DATETIME;
DECLARE @Gradd19 AS VARCHAR (20) = '2016-11-15';
SELECT @FromDate = CONVERT(VARCHAR, @Gradd19, 105);
SELECT @FromDate;
Incase if it is in yyyy-dd-mm
it will throw out-of-range value error
DECLARE @FromDate1 DATETIME;
DECLARE @Gradd191 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate1 = CONVERT(VARCHAR, @Gradd191, 105);
SELECT @FromDate1;
So in that case you need do one more conversion to skip the issue, like the below:
DECLARE @FromDate2 DATETIME;
DECLARE @Gradd192 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate2 = CONVERT(VARCHAR(19), CONVERT(DATETIME, @Gradd192, 105), 120)
SELECT @FromDate2;
So instead of
CONVERT(VARCHAR, Gradd19, 105) AS ExpiryDate
you need to use
CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120) AS ExpiryDate
Update: As you mentioned in the comments the Gradd19
having the values of 20
and 60
, it is difficult to filter those non-valid data. But based on the LEN()
function you may avoid the non-valid data, then using the CONVERT you can get the VARCAHR field as DATETIME value.
Based on your sample data the valid date's minimum length is 10 characters, so you can apply the condition in the WHERE
clause.
Sample execution:
DECLARE @GradedDETAILS TABLE (Gradd19 VARCHAR (30));
INSERT INTO @GradedDETAILS (Gradd19) VALUES
('Sep 16 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('Sep 19 2016 12:00AM'),
('Sep 19 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('20'),
('60'),
('20-09-2016'),
('20-09-2016');
SELECT CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)
FROM @GradedDETAILS
WHERE LEN(Gradd19) >= 10
So in your actual query also you need to add the LEN(Gradd19) >= 10
condition and in the SELECT
use this to convert as DATETIME
by CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)
Best Answer
Well, first off, you should fix your table and store date/time data using the right kind of column, and not breaking it up for reasons unknown. Whose decision was it to store a time as a
CHAR(6)
? Can you think of a single good reason for that? Where do you store the date? Was that 1 PM today, last Tuesday, or October 2012 sometime? This really should be a single datetime column. Have the "designers" please read this and this.But barring that (funny how every time you suggest a table change, they say they can't change the table):
Result:
If you find that query ugly and tedious, good! There's a good reason: You're storing your data wrong.
And this won't be the last problem you have with this design, either. What is stopping someone from inserting
967286
orfoobar
or<empty string>
in yourCHAR(6)
column? If you need to work around that problem, you can useTRY_CONVERT()
in 2012 and up (please always specify version with a version-specific tag), or aCASE
expression in earlier versions.