Estoy tratando de ejecutar una consulta simple para obtener todas las filas creadas en noviembre:
SELECT COUNT(*)
FROM dbo.profile
WHERE [Created] BETWEEN '2014-11-01 00:00:00.000'
AND '2014-11-30 23:59:59.997';
El SMSS devuelve:
La conversión de un tipo de datos varchar a un tipo de datos datetime resultó un valor fuera de rango.
No entiendo por qué los datos se están convirtiendo de varchar a datetime cuando 'Created' está configurado como datetime:
¿Tengo que decirle al servidor que 'Creado' es datetime? Si no es así, ¿por qué recibo este mensaje varchar?
Edición: El valor en la base de datos era AAAA-MM-DD
. La respuesta de @SqlZim a continuación dice que necesito usar convert() para decirle a sql qué formato tiene la fecha en la base de datos - y reemplazar el carácter de espacio con la letra T:
select count(*)
from dbo.profile
where [created] between convert(datetime,'2014-11-01T00:00:00.000')
and convert(datetime,'2014-11-30T23:59:59.997');`
He comprobado tu perfil y he visto que estás en el Reino Unido. Si su servidor sql está configurado para utilizar el formato de fecha dmy entonces eso explica su problema. Si no se utiliza la 'T' en lugar del espacio en la cadena de fecha, sql server no lo reconocerá como formato ISO8601.
Prueba con esto:
select count(*)
from dbo.profile
where [created] between convert(datetime,'2014-11-01T00:00:00.000')
and convert(datetime,'2014-11-30T23:59:59.997');
La consulta utilizando fechas y/o fechas-tiempo puede ser complicada, para asegurarte de que obtienes lo que buscas te recomiendo que leas:
edit: para aclarar que el valor fuera de rango en tu mensaje de error sería por interpretar el mes como 30 y el día como 11.
No entiendo por qué los datos se convierten de varchar a datetime cuando 'Created' está configurado como datetime*.
Los literales que está proporcionando para la comparación con la columna Created
son cadenas. Para comparar esos literales con la columna datetime
, SQL Server intenta convertir las cadenas a tipos datetime
, de acuerdo con las reglas de precedencia de tipos de datos. Sin información explícita sobre el formato de las cadenas, SQL Server sigue sus reglas enrevesadas para interpretar las cadenas como fechas.
Desde mi punto de vista, la mejor manera de evitar este tipo de problemas es ser explícito con los tipos. SQL Server proporciona las funciones CAST y CONVERT
para este propósito. Cuando se trabaja con cadenas y tipos de fecha/hora, se debe preferir CONVERT
porque proporciona un parámetro de estilo para definir explícitamente el formato de la cadena.
La pregunta utiliza cadenas en formato canónico ODBC (con milisegundos) (estilo 121). Ser explícito sobre el tipo de datos y el estilo de la cadena resulta en lo siguiente:
SELECT COUNT(*)
FROM dbo.profile
WHERE [Created] BETWEEN
CONVERT(datetime, '2014-11-01 00:00:00.000', 121)
AND
CONVERT(datetime, '2014-11-30 23:59:59.997', 121);
Dicho esto, hay buenas razones (como señala Aaron en su respuesta) para utilizar un rango medio abierto en lugar de Entre
(utilizo el estilo 120 a continuación sólo para variar):
SELECT COUNT(*)
FROM dbo.profile
WHERE
[Created] >= CONVERT(datetime, '2014-11-01 00:00:00', 120)
AND [Created] < CONVERT(datetime, '2014-12-01 00:00:00', 120);
Ser explícito con los tipos es un muy buen hábito que hay que adquirir, sobre todo cuando se trata de fechas y horas.
Dado que BETWEEN
es muy problemático debido al redondeo de los diferentes tipos de fecha/hora y otros problemas, y dado que YYYY-MM-DD
no es un formato seguro sin la incómoda T
, un rango abierto utilizando las fechas completas del estándar ISO sin separadores es un enfoque mucho mejor:
WHERE Created >= '20141101' AND Created < '20141201';