Access y SQL Server calculan la date de forma diferente

Tenemos una herramienta genérica que, entre otros, puede save y mostrar dates. Lo saveá como un número, por ejemplo 41247.

Si lo vuelvo a convertir en una date en Access, recibo 2012/12/04, que es correcto.
[usando Formato (41247; "Fecha general") o Formato (Formato ("41247", "Fecha abreviada"), "Fecha abreviada")]

Si vuelvo a convertir ese número a una date en SQL Server, recibo 2012/12/06, no es correcto. Estoy usando CONVERT (datetime, CONVERT (real, 41247))

¿Por qué la diferencia y qué puedo usar en SQL para solucionarlo?

Así que tuve este problema cuando estaba importando datos de un file de Excel.

Hay dos razones por las que tienes 2 días de diferencia

Motivo 1 En su server SQL el 1 de enero de 1900 es el día 0, mientras que en el acceso es el día 1.

(No he usado Access, pero si ingresa la date 1900-01-01 en Excel y formatea la celda como un número, obtendrá 1).

Reason 2 1900 NO era un año bisiesto. SQL Server lo sabe, pero Access no. Piensa que el 29 de febrero de 1900 existió.

Ejecuta esto en tu Servidor SQL

SELECT DATEDIFF(dd, 0, '1900-01-01') SELECT DATEDIFF(dd, 0, '1900-02-28') SELECT DATEDIFF(dd, 0, '1900-03-01') 

La salida será

 0 58 59 

Pero cuando tratas de correr

 SELECT DATEDIFF(dd, 0, '1900-02-29') 

Obtendrás un error

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

Debido a estas 2 razones, obtienes 2 días más en tu Acceso.

Así que para 2012/12/04 Access devuelve 41247, mientras que SQL Server le dará 41245.

Espero que haya ayudado.

EDITAR

Eche un vistazo a los comentarios de Eric Lippert donde mencionó dos publicaciones de blog realmente interesantes de él y Joel Spolsky.

No hay absolutamente ninguna garantía de que dos motores de bases de datos diferentes vayan a almacenar dates usando el mismo formatting numérico interno. Por lo tanto, no es razonable esperar …

  • tomar un valor de date / hora de acceso,

  • convertirlo a su equivalente numérico de acceso, luego

  • convertir ese número a un valor de date y hora de SQL Server

… y se garantiza que las dates coincidan.

Si necesita pasar valores de date y hora entre entornos de bases de datos, deberá pasarlos en un formatting que sea común para ambos, como una cadena de caracteres no ambigua. (Si tuviera que enfrentar ese problema, me inclinaría a intentar algo así como el formatting de date / hora que usa XML).

Editar

Con respecto a la respuesta muy informativa de @roughnex, aunque puede ser muy tentador simplemente restar 2 del número de date de acceso para get el número de date de SQL Server (a less que sea <= 58, en cuyo caso restas 1) , no es realmente una buena idea. Para get una explicación de por qué, consulte la siguiente input de blog

Psicología de errores – Eric Lippert

Los atajos de esa índole pueden llevar a comportamientos aparentemente extraños, lo que puede boost el desgaste de los desarrolladores (y también de los usuarios) más adelante.