Manejo de Fechas sql server

¿Como manejar las fechas en Sql Server?

Esta pregunta debe ser una de las que mas se hacen. Este artículo tratara de explicar como usar las Fechas en Sql Server y que cosas debemos tomar en consideración.
Antes de empezar voy a tratar de explicar que son las Fechas para SqlServer:
Este mismo tiene básicamente dos tipos de datos donde se pueden almacenar fechas propiamente dichas: Datetimey SmallDateTime, en este cuadro veremos las diferencias entre estos dos tipos de Datos.

DateTime
Valores de Fecha y Hora que están comprendidos entre 1/1/1763 y 31/12/9999. La hora se expresa con una exactitud de hasta 1/300 de segundo
SamallDateTime
Valores de Fecha y Hora que están comprendidos entre 1/1/1900 y 6/6/2079. El grado de precisión de la hora llega hasta el minuto

Bien ahoraconocemos los tipos de datos de nuestro motor, pero la gran pregunta, ¿Cómo Guarda internamente Sql Server las Fechas? ¿Lo hace en formato MM/DD/YYY o lo hará en DD/MM/YYYY?, bueno lamento decirles que nuestro motor siempre guarda las fechas de una sola forma y no esta referida a ningún formato (Americano,Español,Japones) como recién mencione
¿Entonces? Bueno Sql Server guarda las fechasDateTime como enteros de 4 Bytes (Los primeros 4 Bytes almacenan la Fecha y los otros 4 Bytes la Hora), en SmallDatetime como tienen menor precisión en lugar de ser dos grupos de 4 son dos grupos de 2.

Ahora sabemos también no solo que tipos de datos tenemos para guardar nuestras fechas y horas sino que también como las guarda el motor realmente.
Con todo esto me estoy preguntando, ¿Entonces porquecuando hago algún Select me trae las fechas en un formato X (como por ejemplo el Americano)?
Claro esto es así porque leer las fechas en grupos de Bytes no es muy elegante que digamos y nuestros usuarios no estarían muy contentos.
Bueno ahora entonces tenemos un dilema, ya que cuando quiera hacer una consulta por ejemplo entre fechas deba primero saber que tipo de Formato usar(Americano,Español,etc) ya que no es lo mismo 10-01-2004 (donde 10 es el día y 01 el mes) a 10-01-2004 (Donde 10 es el mes y 01 el día).

Una de las técnicas que veo que mas se usan es aplicar siempre un formato y con eso parece estar la cosa controlada, claro que no es para nada así ya que el formato dependerá de que idioma tenga definido nuestro usuario por lo cual esto nos puede traer muchas complicaciones.Entonces, ¿Cómo hacer una consulta de fechas en un formato y no morir en el intento?, bueno acá hay una solución muy interesante, y es el uso del Standard ANSI para las fechas, este Standard esta compuesto así: YYYYMMDD HH:mm:ss’ , si yo empiezo a utilizar este formato para mis consultas no tendré problemas en ninguna de mis aplicaciones por mas que nuestro usuario este configurado en Ingles,Español o lo que fuere, ni tampoco si en un Windows tengo definido en su configuración regional cualquier cosa.

Ahora tenemos un poco de teoría, pero la misma debe ir acompañada con alguna práctica no? Las cosas hay que demostrarlas para que nos convenzan, por lo menos es mi forma de hacer y pedir las cosas.

Para nuestro ejemplo utilizaremos la Base de Datos Northwind que ya viene como ejemplo ennuestro Sql Server, de no tenerla favor de instalarla desde el instalador correspondiente.

Nota: De aquí en adelante usaremos el Analizador de consultas (Query analizer), pero recordemos que cuando desarrollamos aplicaciones las consultas las hacemos vía la aplicación, al final del articulo hablaremos un poco mas de este tema.

/* Creamos un usuario donde su idioma predefinido es el Español*/

sp_addlogin 'UserFechas','pepe','master','Español'

/* vamos a darle acceso a nuestra Base de Datos Northwind */

use Northwind
GO
sp_grantdbaccess 'UserFechas'
GO

Ahora salimos de nuestro QA (Analizador de Consultas) y volveremos a entrar con este nuevo usuario que creamos (Recuerden que la autentificación de nuestro motor en este caso debe ser Mixta y que el uso de cuentas...