Supongamos que nos piden hacer un query que nos extraiga los 5 últimos registros insertados en una tabla por su fecha y que el formato de la fecha sea mm/dd/aaaa.
Primero haríamos un query sin traer ese formato de la siguiente forma:
select top 5 EventTime ,spid from tabla order by EventTime desc
EventTime spid
------------------------------------------------------ ------
2005-09-16 12:12:14.797 52
2005-09-16 12:12:14.797 65
2005-09-16 11:15:08.357 115
2005-09-16 11:15:08.357 59
2005-09-16 11:10:08.563 102
Ahora si le diéramos formato a la fecha sería de la siguiente manera
select top 5 convert(char(10),EventTime,101) as EventTime ,spid from tabla order by EventTime desc
EventTime spid
---------- ------
09/16/2005 102
09/16/2005 53
09/16/2005 67
09/16/2005 99
09/16/2005 104
Pero si observamos el resultado en realidad no esta correcto pues los datos están ordenados de una manera distinta.
Que es lo que paso? Bueno pues el error común que cometemos muchos de nosotros es que nombramos a la columna con un alias en este caso EventTime que es igual al nombre de la columna y lo hacemos con la finalidad de que la columna tenga el mismo nombre que el del campo y no este vacío el nombre de la columna. Pero al hacer eso lo que hacemos en realidad es decirle a SQL que me ordene el resultado de convertir el campo datetime a caracter, entonces estamos ordenando caracteres en la consulta y no la columna datetime.
En este caso deberíamos de mejor colocarle otro nombre a la columna y no el mismo que trae la tabla para evitar este tipo de error, como por ejemplo.
select top 5 convert(char(10),EventTime,101) as Event_Time ,spid from tabla order by EventTime desc
Event_Time spid
---------- ------
09/16/2005 52
09/16/2005 65
09/16/2005 115
09/16/2005 59
09/16/2005 102
Todo esto lo podemos también ver en el plan de ejecución generado por cada consulta.
select top 5 convert(char(10),EventTime,101) as EventTime ,spid from msdb..dba_processes order by EventTime desc
Plan de ejecución del query erróneo
select top 5 convert(char(10),EventTime,101) as EventTime ,spid from msdb..dba_processes order by EventTime desc
|--Sort(TOP 5, ORDER BY:([Expr1002] DESC))
|--Compute Scalar(DEFINE:([Expr1002]=Convert([dba_processes].[EventTime])))
|--Table Scan(OBJECT:([msdb].[dbo].[dba_processes]))
select top 5 convert(char(10),EventTime,101) as Event_Time ,spid from msdb..dba_processes order by EventTime desc
Plan de ejecución del query correcto
select top 5 convert(char(10),EventTime,101) as Event_Time ,spid from msdb..dba_processes order by EventTime desc
|--Compute Scalar(DEFINE:([Expr1002]=Convert([dba_processes].[EventTime])))
|--Sort(TOP 5, ORDER BY:([dba_processes].[EventTime] DESC))
|--Table Scan(OBJECT:([msdb].[dbo].[dba_processes]))
Si observamos los dos planes de ejecución, el sort se realiza en diferente orden, para el query erróneo primero se hace un table scan, después con los resultados de hace el compute donde realiza el convert y finalmente con ese resultado hacer el sort.
Ahora analizando el correcto, primero hace un table scan, después hace el sort con los datos extraídos usando el tipo de dato datetime y finalmente hace el compute.
Conclusión
Recordemos siempre que cuando vayamos a realizar un sort de un tipo de datos datetime siempre estemos ordenando el tipo de dato correcto y después del sort convertirlo a varchar. Si llegáramos a tener dudas de estar haciendo lo correcto podemos ayudarnos del plan de ejecución.