I have a table whose record has a date, but this date is saved as VARCHAR
, they told me that I could convert it DATE
to SELECT
. This is what I have:
SELECT * FROM tabla WHERE CONVERT (date(15), fecha, 103) = '1/01/2017';
But it gives me the following error:
Msg 291, Level 16, State 1, Line 13 CAST or CONVERT: invalid attributes specified for type 'date'
the records of the table are with this format inVARCHAR
16/06/2016
20/06/2016
20/06/2016
16/07/2016
Imagine data like this:
In the end I put an incorrect value on purpose:
40/01/2017
.My test column is called
fecha_var
.This query:
It will fetch the data, but will give the following error:
And written like this:
It will fetch only the first three rows and give the same error as before.
Solutions
A big problem when assigning data types that don't match (as is the case here when using a
VARCHAR
when it should be aDATETIME
), is the errors that are made when entering the data.It means that there may be invalid dates in the column. Then you have to contemplate that possibility.
Let's look at two possible solutions.
Solution 1: SQL-SERVER 2012+
In SQL-Server 2012 and later there is the function
TRY_CONVERT
which:The query with
TRY_CONVERT
would be:Solution 2: SQL-Server 2012-
You can solve it with this query:
Result:
As you can see, the query returns the Epoch date (
01.01.1900
) when the values are wrong.Cure the problem at the root
Although you say you don't have access to modify the database. If you did, the problem would be solved by creating a new column in the table, called for example
FECHADATE
, and doing aUPDATE
:SQL Server 2012+
SQL-Server 2012-
Result:
You will then have to do a check, checking for dates with Epoch (01-01-1900), indicating that the value in the old column
VARCHAR
was not a valid date. You will have to correct in some way, finding out what was the correct date that should go in that column.[broma]Esperemos que no hayan despedido al que digitó esos datos y se acuerde cual era la fecha correcta y que explique por qué estaba dormido ese día y cuántos errores como ese habrá cometido.[/broma]
As already explained in @Marcos Pérez's answer, the correct syntax for the conversion is:
But that same conversion you have to apply to the date with which you compare:
...or, to simplify a bit, you can use the format
YYYYMMDD
that doesn't require an explicit conversion:If despite these changes you still get errors, that means that not all your data follows the format
DD/MM/YYYY
as you mention in your question. Those are the risks of using wrong type to save the data. It will be up to you to get the records that have an incorrect format and correct them.You would simply have to use the variable type datetime instead of date(15) for that convert to work well for you. I enclose the link of the original post in case you want to take a look, as well as the final query. original post