Menu

Configuración del Formato de Fechas en SQL SERVER

16/09/2011 - Code Snippet, Sql Server

 

Aunque lo ideal es encapsular las variables de fecha en parámetros ya formateados en ocasiones (sobretodo en proyectos viejos) te encuentras con algunas consultas con las fechas directamente en string como el típico:

SELECT * FROM Pedidos WHERE FechaPedido >= '30/01/2011'

Es posible que esta consulta funcione correctamente en tu servidor de SQL SERVER local pero al llevarlo a producción te encuentres en ocasiones con el típico error:

La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo / The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Este error ocurre cuando el servidor intenta comparar una columna con formato “DateTime” (como es el campo “FechaPedido” de nuestro ejemplo) con un String pasado como parámetro y, al intentar convertirlo se desborda.

¿Por qué ocurre esto? Porque el idioma del servidor le indica que debe interpretar las fechas de otro modo y al convertir el String en Date da un valor incorrecto (lo más clásico es que lo intente convertir en MM/DD/YYYY y como el mes 30 no existe pasa lo que pasa…).

 

Hay varias formas de solucionar este problema aunque, como he dicho al principio del post, lo ideal es pasar la fecha en una variable ya formateada como Date usando tecnologías de acceso a datos como Entity Framework, Linq o con los DataSets de ADO.NET.

 

Como SysAdmin: Modificar la configuración global de idioma del servidor

Si queremos modificar el idioma predeterminado de TODO el servidor para TODAS las bases de datos podemos hacer los siguiente:

Como “sa” y contra “master” ejecutamos lo siguiente:

SP_CONFIGURE 'default language', 5

RECONFIGURE;

Dónde el 5 pasado como parámetro es el idioma “Español” con formato de fecha “dd/mm/yyyy”.

Si queremos establecer otro idioma y no sabemos su identificador usad esta consulta:

USE master
SELECT * FROM syslanguages

De este modo todo nuevo login contra el servidor de base de datos usará este idioma por defecto y afectará a toda conversión de fecha que se solicite (como en la SELECT del ejemplo, un DATEDIFF o un DATENAME para obtener el nombre del día o mes).

 

Como SysAdmin: Modificar la configuración de idioma de un login en concreto

Si no queremos cambiar la configuración de idioma de todo el servidor (cosa lógica si hay otras bases de datos funcionando y no queremos modificar su comportamiento) podemos modificar sólo la configuración de idioma de un login en concreto para que cualquier consulta contra cualquier base de datos que lance dicho login utilice el idioma indicado.

La consulta para cambiar el idioma de un login es:

EXEC sp_defaultlanguage 'nombre del login', 'idioma'

Dónde “idioma” es el nombre del idioma que queremos establecer que podemos obtener del campo “name” de la tabla “master.syslanguages” tal y cómo hemos explicado antes.

 

Al igual que con el otro método es necesario que el usuario vuelva a reconectarse para hacer uso de la nueva configuración.

 

Como desarrollador: Establecer el idioma antes de hacer una consulta

Si no tienes más remedio que hacer una consulta pasando fechas como string puedes asegurarte de escoger el formato antes de realizar la consulta. Para ello basta con añadir la siguiente clausula ANTES de hacer la consulta:

SET LANGUAGE 'idioma'

Con lo que podríamos hacer que nuestra consulta de ejemplo funcione siempre en cualquier servidor sea cual sea el idioma del mismo haciendo la consulta así:

SET LANGUAGE 'español';

SELECT * FROM Pedidos WHERE FechaPedido >= '30/01/2011';

De este modo nos aseguramos que la conversión de la fecha siempre sea con formato del idioma “español” que es dd/mm/yyyy

 

Depende del escenario que tengas deberás usar un método u otro aunque, lo siento por repetirme, lo mejor es controlar el formato de la fecha a nivel de aplicación y no ir modificando la configuración del servidor.

 

 

Nos vemos compilando!!

Víctor Fernández Portero on twitterVíctor Fernández Portero on linkedinVíctor Fernández Portero on github
Víctor Fernández Portero
Lead Developer at Terminis
¡Hola! Me llamo Víctor Fernández Portero y soy ingeniero informático y amante de la ingeniería del software a la que llevo dedicándome desde el 2002.
He podido trabajar en varios tipos de proyectos a lo largo de estos años, desde aplicaciones locales en entornos Windows hasta webs en entornos cloud como Azure.
Actualmente soy el lead developer en Terminis, un proyecto que me ha aportado mucho tanto a nivel personal como profesional y en el que he tenido oportunidad de trabajar con grandes profesionales del sector así como de emplear las últimas metodologías y tecnologías .NET

7 thoughts on “Configuración del Formato de Fechas en SQL SERVER

Miguel

Buen aporte. Justo lo que necesitaba. :)

Reply
cesar david fernandez

Excelente amigo, por fin logre registrar datos fecha desde un objeto DateTimePicker Visual Basic .NET al servidor SQL 2008. Busque en demasiadas paginas y la única solución que me sirvió fue la tuya. MUCHAS GRACIAS… Saludos desde Colombia.

Reply
Sandy

Muchas Gracias :)

Reply
Marlon

Gracias Me sirvio Mucho 😉

Reply
jose luis

Estimado perdón si mi pedido es muy básico, es posible mostrar un ejemplo de como seria la consulta correcta ocupando Entity Framework, Linq o con los DataSets de ADO.NET.

Reply
Froy PM

Excelente hermano, me salvaste la vida… jeje.

Reply
Roy Terrazas

Hola recientemente empece a desarrollar en webmatrix, y su base de datos sdf, sin embargo requiero cambiar la bd a sql server, lo empece a hacer a mano pero me tope con el siguiente problema

la base de datos sdf me da este formato de fecha
7/17/2015 11:55:59 PM
y la de sql no lo acepta, solo me acepta:
2015-07-17
que cambio hago para lograr esto desde la base de datos y no antes de acceder el dato a la bd, esto por que la fecha se mete con las funciones nativas de .net a la bd

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>