Convert VARCHAR to DATE in a SELECT

Imagine data like these:

fecha_var
1/01/2017
2/01/2017
3/01/2017
40/01/2017

At the end I put a wrong value on purpose: 40/01/2017 .

My test column is called fecha_var .

This query:

SELECT * 
FROM fechas_20171115 
WHERE CONVERT (datetime, fecha_var, 103) = '01/01/2017';

It will bring the data, but it will give the following error:

  

The conversion of a varchar data type to a datetime data type results   in an out-of-range value.

And written like this:

SELECT 
    CONVERT (datetime, fecha_var, 103) 
FROM fechas_20171115;

It will only bring the first three rows and give the same error as before.

Solutions

A big problem when we assign data types that do not correspond (as is the case here when using a VARCHAR when it should be a DATETIME ), are the errors that are committed when entering the data.

It means that there may be invalid dates in the column. Then you have to contemplate that possibility.

Let's see two possible solutions.

Solution 1: SQL-SERVER 2012 +

In SQL Server 2012 and later, the % function exists TRY_CONVERT which:

  

Returns a value conversion to the specified data type if the   conversion is successful; otherwise, it returns NULL .

The query with TRY_CONVERT would be:

SELECT
    (TRY_CONVERT(date, fecha_var, 103)) as FECHA_DATE
FROM fechas_20171115;

Solution 2: SQL-Server 2012 -

You can solve it with this query:

SET LANGUAGE british;

SELECT
    (CASE WHEN 
        (ISDATE(fecha_var) > 0) 
             THEN CONVERT(DATE, FECHA_VAR) 
             ELSE CONVERT(DATE, '01/01/1900') 
     END) as FECHA_DATE
FROM fechas_20171115;

Result:

    FECHA_DATE
    01.01.2017 00:00:00
    01.02.2017 00:00:00
    01.03.2017 00:00:00
    01.01.1900 00:00:00

As you can see, the query brings you the Epoch date ( 01.01.1900 ) when the values are incorrect.

Cure the problem at the root

Although you say that you do not have access to modify the database. If you had it, the problem would be solved by creating a new column in the table, called for example FECHADATE , and doing a UPDATE :

SQL-Server 2012 +

UPDATE  fechas_20171115 
SET FECHA_DATE = (SELECT (TRY_CONVERT(date, fecha_var, 103)) as TMP);

SQL-Server 2012 -

SET LANGUAGE british;
UPDATE  fechas_20171115 SET FECHA_DATE =
(    SELECT
        (CASE WHEN 
            (ISDATE(fecha_var) > 0) 
                 THEN CONVERT(DATE, FECHA_VAR) 
                 ELSE CONVERT(DATE, '01/01/1900') 
         END) as TMP
 );


--Probamos ahora lo que ha ocurrido

SELECT * FROM fechas_20171115;

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'fechas_20171115'
ORDER  BY ordinal_position;

Result:

id  fecha_var   FECHA_DATE
1   1/01/2017   01.01.2017 00:00:00
2   2/01/2017   01.02.2017 00:00:00
3   3/01/2017   01.03.2017 00:00:00
4   40/01/2017  01.01.1900 00:00:00

column_name    data_type
id             int
fecha_var      varchar
FECHA_DATE     datetime

Then you will have to do a review, checking if there are dates with Epoch (01-01-1900), indicating that the value of the old column VARCHAR was not a valid date. You will have to correct in some way, investigating 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]

You just have to use the datetime variable type instead of date (15) for that convert to work well. I attached the link to the original post in case you want to take a look, as well as the final query. Original post

   SELECT * FROM tabla WHERE CONVERT (datetime, fecha, 103) = '01/01/2017';

As already explained in the response of @Marcos Pérez, the correct syntax for the conversion is:

convert(datetime, fecha, 103)

But that same conversion you have to apply to the date with which you compare:

SELECT * FROM tabla WHERE CONVERT(datetime, fecha, 103) = CONVERT(datetime, '01/01/2017', 103)

... or, to simplify a bit, you can use the YYYYMMDD format that does not require an explicit conversion:

SELECT * FROM tabla WHERE CONVERT(datetime, fecha, 103) = '20170101'

If in spite of this changes you are still receiving errors, that means that not all your data follow the format DD/MM/YYYY as you mention in your question. Those are the risks of using an incorrect type to store the data. You will have to get the records that have an incorrect format and correct them.