Compartir a través de


Usar datos de fecha y hora

Las secciones siguientes de este tema incluyen información y ejemplos sobre el uso de los tipos de datos y funciones de fecha y hora. Para obtener información general de todos los tipos de datos y funciones de fecha y hora de Transact-SQL, consulte Tipos de datos y funciones de fecha y hora (Transact-SQL).

  • Usar operadores con tipos de datos de fecha y hora

  • Usar formatos de fecha y hora

  • Formatos de fecha y hora del literal de cadena

  • Formato de cadena sin separar

  • Formato ISO 8601

  • Formatos alfabéticos de fecha

  • Formatos numéricos de fecha

  • Formatos de hora

  • Formato de fecha y hora de ODBC

  • Convertir fecha, hora, datetime2 y datetimeoffset

  • Conversión entre literales de cadena y hora(n), fecha, datetime2(n) y datetimeoffset(n)

  • Conversión entre los tipos de datos de fecha y hora

  • Usar CAST y CONVERT con hora, fecha, datetime2 y datetimeoffset

  • Argumentos de estilo de la función CONVERT

  • Limitaciones de fecha y hora de SQL Server

  • Compatibilidad con versiones anteriores de clientes de niveles inferiores

  • Ejemplos

Usar operadores con tipos de datos de fecha y hora

Los operadores relacionales (<, <=, >, >=, <>), los operadores de comparación (=, <, <=, >, >=, <>, !<, !>), los operadores lógicos y los predicados booleanos (IS NULL, IS NOT NULL, IN, BETWEEN, EXISTS, NOT EXISTS y LIKE) se admiten en todos los tipos de datos de fecha y hora.

Operadores aritméticos de fecha y hora

Para sumar y restar en todos los tipos de datos de fecha y hora, use DATEADD y DATEDIFF.

Usar formatos de fecha y hora

Los formatos del literal de cadena afectan a la presentación de datos en aplicaciones de los usuarios, pero no al formato subyacente de almacenamiento de enteros en SQL Server. Sin embargo, SQL Server podría interpretar un valor de fecha en un formato del literal de cadena, proporcionado por una aplicación o usuario para el almacenamiento o a una función de fecha, como fechas diferentes. La interpretación depende de la combinación de formato del literal de cadena, del tipo de datos y de la configuración en tiempo de ejecución de SET DATEFORMAT, SET LANGUAGE y de la opción de idioma predeterminada.

Estos valores no afectan a algunos formatos del literal de cadena. Considere la posibilidad de usar un formato que no dependa de estos valores, a menos que sepa que la configuración del formato es correcta. El formato ISO 8601 no depende de estos valores y es un estándar internacional. Transact-SQL, que usa formatos del literal de cadena dependientes de la configuración del sistema, es menos portátil.

Para averiguar el formato del literal de cadena predeterminado de los clientes de niveles inferiores, consulte el tema de cada tipo de datos de fecha y hora. Para obtener información general de todos los tipos de datos y funciones de fecha y hora de Transact-SQL, consulte Tipos de datos y funciones de fecha y hora (Transact-SQL).

El formato de fecha ydm no se admite para los tipos date, datetime2 y datetimeoffset. Se producirá un error en el tiempo de ejecución.

Formatos de fecha y hora del literal de cadena

En la tabla siguiente se enumeran diferentes formatos de cadena de fecha y hora. Se recomienda usar formatos de fecha y hora que no dependan de DATEFORMAT y que sean multilingües. Los formatos de ISO 8601 '1998-02-23T14:23:05' y '1998-02-23T14:23:05 -08:00' son los únicos formatos que son un estándar internacional. No dependen de DATEFORMAT o del lenguaje de inicio de sesión predeterminado y son multilingües.

Parte de fecha y hora

Tipo de formato

Ejemplo de formato

Se puede combinar con otros formatos

Depende de DATEFORMAT

Multilingüe

Fecha

Sin separar

ISO 8601

'19980223'

No

Fecha

Numérico

'02/23/1998'

No

(DATEFORMAT)

Fecha

ISO 8601 Numérico

'1998-02-23'

No

No

Fecha

Alfabético

'23 de febrero de 1998'

No

No

(mes o abreviatura del mes)

Fecha

Fecha ODBC

{d '1998-02-23'}

No

No

Hora

Hora ISO 8601

'14:23:05'

'10:00:00.123456'

No

Hora

Hora ODBC

{t '14:23:05'}

No

No

Fecha/hora

Fecha y hora ODBC

{ts '1998-02-23 14:23:05'}

No

No

Fecha/hora

ISO 8601

'1998-02-23T14:23:05'

'1998-02-23T14:23:05 -08:00'

No

No

Sí date, datetime2, datetimeoffset.

Fecha/hora

Estándar ANSI SQL

'1998-02-23 14:23:05'

'1998-02-23 14:23:05 -08:00'

No

No (datetime2, datetimeoffset)

Sí (datetime)

Sí date, datetime2, datetimeoffset.

Fecha/hora

Combinación de fecha y hora

'19980223 14:23:05'

'02/23/1998 2:23:05 PM'

'1998-02-23 10:00:00.123'

'23 Feb 1998 14:23:05'

No

(parte de la fecha)

No

Zona horaria

Formato de zona horaria

'+12:00'

'01:00'

'-08:00'

'Z'

No

Las instrucciones siguientes muestran los efectos de los valores SET LANGUAGE y SET DATEFORMAT.

DECLARE @Today date = '12/1/2003';
DECLARE @MyTime time = '12:30:22';
DECLARE @MyDatetimeoffset datetimeoffset = '12/1/2003 12:30:22 -5:00';
SET LANGUAGE Italian
    -- Returns: Changed language setting to Italiano.
SELECT DATENAME(month, @Today) AS 'Month Name';
    -- Returns: dicembre
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
    -- Returns: dic  1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
    -- Returns: 12

SET LANGUAGE us_english;
    -- Returns: Changed language setting to us_english.
SELECT DATENAME(month, @Today) AS 'Month Name';
    -- Returns: December
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
    -- Returns: Dec  1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
    -- Returns: 12
GO
-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
    -- Returns: 1998-12-31
GO
-- Set date format to year, day, month. 
SET DATEFORMAT ydm;
GO
DECLARE @datevar datetimeoffset = '1998/31/12 12:30:22 -05:00';
SELECT @datevar AS DateVar;
    -- Returns: Msg 241, Conversion failed when converting 
    -- date and/or time from character string.
GO
-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
    -- Returns: 1998-12-31
GO

Formato de cadena sin separar

Puede especificar datos de fecha como una cadena sin separar. Los datos de fecha se pueden especificar utilizando cuatro, seis u ocho dígitos, una cadena vacía o un valor de hora sin un valor de fecha.

La configuración de sesión SET DATEFORMAT no se aplica a las entradas de fecha totalmente numéricas como, por ejemplo, las entradas numéricas sin separadores. Las cadenas de seis u ocho dígitos se interpretan siempre como ymd. El mes y el día deben ser siempre de dos dígitos.

Éste es el formato válido de una cadena sin separar: [19]960415 [19]960415

Una cadena de sólo cuatro dígitos se interpreta como el año. El mes y la fecha están establecidos en el 1 de enero. Cuando se especifican sólo cuatro dígitos, es necesario incluir el siglo.

Formato ISO 8601

La fecha de ISO 8601 con formato de hora es la siguiente:

  • AAAA-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]

  • AAAA-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC, hora universal coordinada)

La T indica el inicio de la parte de la hora del valor de fecha y hora.

Para usar el formato ISO 8601, debe especificar todos los elementos del formato. Esto incluye la T, los dos puntos (:), los signos + o - y los puntos (.). Los corchetes indican que las fracciones de segundo o los componentes de desplazamiento de zona horaria son opcionales.

El componente de hora se especifica con el formato de 24 horas.

La ventaja de utilizar el formato ISO 8601 es que se trata de un estándar internacional. Los valores de fecha y hora que se especifican con este formato evitan la ambigüedad. Este formato no se ve afectado por los valores de SET DATEFORMAT, de SET LANGUAGE o del idioma predeterminado de inicio de sesión.

A continuación se exponen dos ejemplos de valores de fecha y hora establecidos en el formato ISO 8601:

  • 2004-05-23T14:25:10

  • 2004-05-23T14:25:10.1234567+07:00

Formatos de fecha alfabéticos

Puede especificar un mes como un nombre, por ejemplo, abril o su abreviatura en español Abr. Estos valores se deben especificar en el parámetro LANGUAGE de la sesión, por ejemplo, avril o avr para francés. Las comas son opcionales y se omite el uso de mayúsculas.

Éstas son algunas directrices para utilizar los formatos alfabéticos de fecha:

  • Incluya los datos de fecha y hora entre comillas simples (').

  • Si sólo especifica los dos últimos dígitos del año, los valores inferiores a los dos últimos dígitos del valor de la opción de configuración Fecha límite de año de dos dígitos pertenecen al mismo siglo que el año límite. Los valores mayores o iguales que el valor de esta opción pertenecen al siglo anterior al año límite. Por ejemplo, si el valor de Fecha límite de año de dos dígitos es 2050 (el valor predeterminado), 25 se interpreta como 2025 y 50 se interpreta como 1950. Para evitar la ambigüedad, utilice años de cuatro dígitos.

  • Si falta el día, se usará el primer día del mes.

  • El parámetro de sesión SET DATEFORMAT no se aplica cuando se especifica el mes de forma alfabética.

  • Los siguientes formatos son los formatos alfabéticos válidos de fecha y hora de SQL Server. Los caracteres que se incluyen entre corchetes son opcionales.

  • Abr[il] [15][,] 1996

  • Abr[il] 15[,] [19]96

  • Abr[il] 1996 [15]

  • [15] Abr[il][,] 1996

  • 15 Abr[il][,][19]96

  • 15 [19]96 abr[il]

  • [15] 1996 abr[il]

  • 1996 ABR[IL] [15]

  • 1996 [15] ABR[IL]

Formatos de fecha numéricos

Puede especificar los datos de la fecha con un mes numérico. Por ejemplo, 5/20/97 representa el veinte de mayo de 1997. Cuando use un formato numérico de fecha, especifique el año, el mes y el día en una cadena con marcas de barras diagonales (/), guiones (-) o puntos (.) como separadores. Esta cadena debe aparecer de la forma siguiente:

número separador número separador número [hora] [hora]

Los siguientes formatos numéricos son válidos:

  • [0]4/15/[19]96 -- (mda)

  • [0]4-15-[19]96 -- (mda)

  • [0]4.15.[19]96 -- (mda)

  • [0]4/[19]96/15 -- (mad)

  • 15/[0]4/[19]96 -- (dma)

  • 15/[19]96/[0]4 -- (dam)

  • [19]96/15/[0]4 -- (adm)

  • [19]96/[0]4/15 -- (amd)

El DATEFORMAT del idioma predeterminado para una sesión lo establece el idioma predeterminado para el inicio de sesión, una instrucción SET LANGUAGE o una instrucción SET DATEFORMAT. Cuando el idioma se ha establecido en us_english mediante el inicio de sesión predeterminado o la instrucción SET LANGUAGE, el orden predeterminado de la fecha será mdy.

La instrucción SET DATEFORMAT permite cambiar el orden de la fecha. La configuración de SET DATEFORMAT determina cómo se interpretan los valores de fecha. Si el orden no coincide con esta configuración, los valores no se interpretarán como fechas porque se encuentran fuera del intervalo, o bien se interpretarán incorrectamente. Por ejemplo, 12/10/08 se puede interpretar de seis formas distintas, en función de la configuración de DATEFORMAT. Un año de cuatro dígitos se interpretará como el año.

Formatos de hora

SQL Server reconoce los siguientes formatos de datos de hora. Incluya cada formato entre comillas simples (').

  • 14:30

  • 14:30[:20:999]

  • 14:30[:20.9]

  • 4am

  • 4 PM

  • [0]4[:30:20:500]AM

Las instrucciones siguientes muestran los valores de entradas diferentes devueltos a la función CAST.

SELECT CAST('01/01/2000 14:30' AS datetime2)
    --Returns: 2000-01-01 14:30:00.0000000
SELECT CAST('01/01/2000 14:30:20:999' AS datetime2)
    -- Returns: 2000-01-01 14:30:20.9990000
SELECT CAST('01/01/2000 14:30:20.9' AS datetime2)
    -- Returns: 2000-01-01 14:30:20.9000000
SELECT CAST('01/01/2000 4am' AS datetime2)
    -- Returns: 2000-01-01 04:00:00.0000000
SELECT CAST('01/01/2000 4 PM' AS datetime2)
    -- Returns: 2000-01-01 16:00:00.0000000
SELECT CAST('01/01/2000 04:30:20:500AM' AS datetime2)
    -- Returns: 2000-01-01 04:30:20.5000000
SELECT CAST('01/01/2000 04:30:20:500 AM' AS datetime2)
    -- Returns: 2000-01-01 04:30:20.5000000
GO

Puede especificar el sufijo AM o PM para indicar si el valor de la hora es anterior o posterior a las 12 del mediodía. No se distingue entre mayúsculas y minúsculas en AM o PM.

Las horas se pueden especificar con el reloj de 12 o de 24 horas. Los valores de hora se interpretan del siguiente modo:

  • El valor de hora 00 representa la hora desde medianoche (AM), sin tener en cuenta si se especifica AM. No puede especificar PM cuando la hora es igual a 00.

  • Las horas de 01 a 11 representan horas antes del mediodía si no se especifica AM ni PM. Cuando se especifica AM también representan las horas antes del mediodía. Si se especifica PM representan las horas después del mediodía.

  • El valor de hora 12 representa el mediodía si no se especifica AM ni PM. Si se especifica AM, representa la medianoche. Si se especifica PM, representa el mediodía. Por ejemplo: 12:01 es 1 minuto después del mediodía, igual que 12:01 PM, mientras que 12:01 AM es 1 minuto después de medianoche. Especificar 12:01 AM es lo mismo que 00:01 ó 00:01 AM.

  • Los valores de hora de 13 a 23 representan horas después del mediodía si no se especifica AM o PM. Estos valores representan las horas después del mediodía cuando también se especifica PM. No es posible especificar AM cuando el valor de hora es de 13 a 23.

  • El valor de hora 24 no es válido; use 12:00 AM ó 00:00 para representar la medianoche.

Los milisegundos pueden ir precedidos de dos puntos (:) o de un punto (.). Si van precedidos de dos puntos, el número significa milésimas de segundo. Si van precedidos de un punto, un único dígito hace referencia a las décimas de segundo, dos dígitos a las centésimas de segundo y tres dígitos a las milésimas de segundo. Por ejemplo, 12:30:20:1 indica las 12:30, veinte segundos y una milésima; 12:30:20.1 indica las 12:30, veinte segundos y una décima.

Formato de fecha y hora ODBC

La API de ODBC define secuencias de escape que representan valores de fecha y de hora que ODBC llama datos de marca de tiempo. La definición del lenguaje OLE DB (DBGUID-SQL) y el proveedor OLE DB de Microsoft para SQL Server también admiten este formato de marca de tiempo de ODBC. Las aplicaciones que usan las API basadas en ODBC, OLE DB y ADO pueden utilizar este formato de marca de tiempo de ODBC para representar fechas y horas.

SQL Server siempre trata los datos de ODBC como un tipo de datos de datetime.

Las secuencias de escape de marca de tiempo de ODBC tienen el siguiente formato:

{ literal_type 'constant_value' }

  • literal_type
    Especifica el tipo de la secuencia de escape. Los argumentos válidos para literal_type son los siguientes:

    d = sólo fecha

    t = sólo hora

    ts = marca de tiempo (hora + fecha)

  • 'constant_value'
    Es el valor de la secuencia de escape. constant_value debe seguir estos formatos para cada literal_type.

    literal_type

    formato constant_value

    d

    AAAA-MM-DD

    t

    hh:mm:ss[.fff]

    ts

    AAAA-MM-DDhh:mm:ss[.fff]

A continuación, se muestran ejemplos de constantes de hora y fecha de ODBC:

  • { ts '1998-05-02 01:23:56.123' }

  • { d '1990-10-02' }

  • { t '13:33:41' }

No confunda el nombre del tipo de datos de marca de tiempo de ODBC y de OLE DB con el nombre del tipo de datos de la Transact-SQLmarca de tiempo. El tipo de datos de marca de tiempo de ODBC y OLE DB guarda fechas y horas. El tipo de datos de timestamp de Transact-SQL es un tipo de datos binario que no tiene valores relacionados con el tiempo.

Convertir fecha, hora, datetime2 y datetimeoffset

Hay dos tipos de conversiones entre los distintos tipos de fechas: explícitas e implícitas. Las conversiones implícitas se producen sin utilizar las funciones CAST o CONVERT. Las conversiones explícitas requieren de las funciones CAST o CONVERT.

Conversión entre literales de cadena y hora(n), fecha, datetime2(n) y datetimeoffset(n)

Las conversiones de literales de cadena en tipos de fecha y hora son posibles cuando todas las partes de las cadenas están en formatos válidos. En caso contrario, se generará un error en el tiempo de ejecución.

Las conversiones implícitas o explícitas que no especifican un estilo (desde tipos de fecha y hora hasta literales de cadena) estarán en el formato predeterminado de la sesión actual.

Para la conversión implícita de tipos de datos de fecha, hora, datetime2 y datetimeoffset a cadenas de caracteres, se aplicará el formato estándar SQL AAA-MM-DD hh:mi:ss.[nnnnnnn], estilo CONVERT 121. El formato de estilo CONVERT 0, mes dd aaaa hh:miAM (o PM) se aplica a los tipos de datos datetime y smalldatetime.

En la tabla siguiente se muestran las reglas para la conversión entre tipos y literales de cadenas de date, time, datetime2 y datetimeoffset.

Literal de cadena de entrada

date

time(n)

datetime2(n)

datetimeoffset(n)

DATE DE ODBC

Vea la nota 1.

Vea la nota 1.

Vea la nota 1.

Vea la nota 1.

TIME DE ODBC

Vea la nota 1.

Vea la nota 1.

Vea la nota 1.

Vea la nota 1.

DATETIME DE ODBC

Vea la nota 1.

Vea la nota 1.

Vea la nota 1.

Vea la nota 1.

Sólo DATE

Trivial

Se proporcionan los valores predeterminados.

La parte de TIME tiene como valor predeterminado 00:00:00.

La parte de TIME tiene como valor predeterminado 00:00:00. TIMEZONE tiene como valor predeterminado +00:00.

Sólo TIME

Se proporcionan los valores predeterminados.

Trivial

La parte de DATE tiene como valor predeterminado 1900-1-1.

La parte de DATE tiene como valor predeterminado 1900-1-1. TIMEZONE tendrá como valor predeterminado +00:00.

Sólo TIMEZONE

Se proporcionan los valores predeterminados.

Se proporcionan los valores predeterminados.

Se proporcionan los valores predeterminados.

Se proporcionan los valores predeterminados.

DATE + TIME

Se usa la parte de DATE de la cadena de entrada.

Se usa la parte de TIME de la cadena de entrada.

Trivial

TIMEZONE tiene como valor predeterminado +00:00.

DATE + TIMEZONE

No permitido

No permitido

No permitido

No permitido

TIME + TIMEZONE

Se proporcionan los valores predeterminados.

Se usa la parte de TIME de la cadena de entrada.

La parte de DATE tiene como valor predeterminado 1900-1-1. Se omite la entrada de TIMEZONE.

La parte de DATE tiene como valor predeterminado 1900-1-1.

DATE + TIME + TIMEZONE

Se usará la parte de DATE de DATETIME local.

Se usará la parte de TIME de DATETIME local.

Se usará DATETIME local.

Trivial

Notas de conversión

  1. Los literales de cadena de ODBC se asignan al tipo de datos de datetime. Cualquier operación de asignación de los literales de DATETIME de ODBC en los tipos de date, time, datetime2 o datetimeoffse provocarán una conversión implícita entre datetime y estos tipos, tal y como se define en las reglas de conversión.

  2. La precisión en segundos fraccionaria de datetime tiene una exactitud de tres centésimas de segundo (equivalente a 3,33 milisegundos o 0,00333 segundos). Los valores se redondean en incrementos de 0,000, 0,003 o 0,007 segundos. '08/22/1995 10:15:19:999' se redondea porque '0,999' supera la precisión.

  3. Para time(3), datetime2(3) o datetimeoffset(3), la precisión en segundos fraccionaria tiene una exactitud de un milisegundo. Por consiguiente, '1995-8-22 10:15:19:999' no se redondeará.

  4. La parte de desplazamiento TIMEZONE de entrada debería ser siempre de dígitos dobles para hh y mm; los signos + o – son obligatorios.

Conversión entre los tipos de datos de fecha y hora

Las tablas de esta sección describen cómo cada uno de los siguientes tipos de datos de fecha y hora se convierte a los demás tipos de datos de fecha y hora:

  • date

  • time(n)

  • datetime

  • smalldatetime

  • datetimeoffset(n)

  • datetime2

Tipo de datos date

La tabla siguiente describe lo que sucede cuando un tipo de datos date se convierte a otros tipos de datos de fecha y hora.

Tipo de datos al que se convierte

Detalles de la conversión

time(n)

La conversión no se produce y se recibe el mensaje de error 206: "Conflicto de tipos de operandos: date es incompatible con time".

datetime

Se copia la fecha. El código siguiente muestra los resultados de convertir un valor de date en un valor de datetime.

DECLARE @date date
DECLARE @datetime datetime
SELECT @date = '12-10-25'
SELECT @datetime = @date
SELECT @date AS '@date', @datetime AS '@datetime'
--Result
--@date      @datetime
------------ -----------------------
--2025-12-10 2025-12-10 00:00:00.000
--
--(1 row(s) affected)

smalldatetime

Cuando el valor de date está en el intervalo de un valor de smalldatetime, se copia el componente de fecha y el componente de hora se establece en 00:00:00.000. Cuando el valor de date está fuera del intervalo de un valor de smalldatetime, se recibe el mensaje de error 242: "La conversión del tipo de datos date en smalldatetime produjo un valor fuera de intervalo", y el valor de smalldatetime se establece en NULL.

El código siguiente muestra los resultados de convertir un valor de date en un valor de smalldatetime.

DECLARE @date date
DECLARE @smalldatetime smalldatetime
SELECT @date = '1912-10-25'
SELECT @smalldatetime = @date
SELECT @date AS '@date', @smalldatetime AS '@smalldatetime'
--Result
--@date      @smalldatetime
------------ -----------------------
--1912-10-25 1912-10-25 00:00:00
--
--(1 row(s) affected)

datetimeoffset(n)

Se copia la fecha, y la hora se establece en 00:00.0000000 +00:00.

El código siguiente muestra los resultados de convertir un valor de date en un valor de datetimeoffset(3).

DECLARE @date date
DECLARE @datetimeoffset datetimeoffset(3)
SELECT @date = '1912-10-25'
SELECT @datetimeoffset = @date
SELECT @date AS '@date', @datetimeoffset AS '@datetimeoffset'
--Result
--@date      @datetimeoffset
------------ ------------------------------
--1912-10-25 1912-10-25 00:00:00.000 +00:00
--
--(1 row(s) affected)

datetime2(n)

Se copia el componente de fecha, y el componente de hora se establece en 00:00.000000.

El código siguiente muestra los resultados de convertir un valor de date en un valor de datetime2(3).

DECLARE @date date
DECLARE @datetime2 datetime2(3)
SELECT @date = '1912-10-25'
SELECT @datetime2 = @date
SELECT @date AS '@date', @datetime2 AS '@datetime2(3)'
--Result
--@date      @datetime2(3)
------------ -----------------------
--1912-10-25 1912-10-25 00:00:00.000
--
--(1 row(s) affected)

Tipo de datos time(n)

La tabla siguiente describe lo que sucede cuando un tipo de datos time se convierte a otros tipos de datos de fecha y hora.

Tipo de datos al que se convierte

Detalles de la conversión

time(n)

Se copian los valores de hora, minuto y segundo. Cuando la precisión de destino es menor que la precisión de origen, las fracciones de segundo se truncarán para ajustarse a la precisión de destino.

El ejemplo siguiente muestra los resultados de convertir un valor de time(4) en un valor de time(3).

DECLARE @timeTo time(3)
DECLARE @timeFrom time(4)
SELECT @timeFrom = '12:34:54.1234'
SELECT @timeTo = @TimeFrom
SELECT @timeTo AS 'time(3)', @timeFrom AS 'time(4)'
--Results
--time(3)      time(4)
-------------- -------------
--12:34:54.123 12:34:54.1234
--
--(1 row(s) affected)

date

La conversión no se produce y se recibe el mensaje de error 206: "Conflicto de tipos de operandos: date es incompatible con time".

datetime

Se copian los valores de hora, minuto y segundo, y el componente de fecha se establece en '1900-01-01'. Cuando la precisión de las fracciones de segundo del valor de time(n) es superior a tres dígitos, el resultado de datetime se truncará.

El código siguiente muestra los resultados de convertir un valor de time(4) en un valor de datetime.

DECLARE @time time(4)
DECLARE @datetime datetime
SELECT @time = '12:15:04.1234'
SELECT @datetime = @time
SELECT @time AS '@time', @datetime AS '@datetime'
--Result
--@time         @datetime
--------------- -----------------------
--12:15:04.1234 1900-01-01 12:15:04.123
--
--(1 row(s) affected)

smalldatetime

La fecha se establece en '1900-01-01', y se copian los valores de hora y minuto. Los segundos y las fracciones de segundo se establecen en 0.

El código siguiente muestra los resultados de convertir un valor de time(4) en un valor de smalldatetime.

DECLARE @time time(4)
DECLARE @smalldatetime smalldatetime
SELECT @time = '12:15:04.1234'
SELECT @smalldatetime = @time
SELECT @time AS '@time', @smalldatetime AS '@smalldatetime'
--Result
--@time         @smalldatetime
--------------- -----------------------
--12:15:04.1234 1900-01-01 12:15:00
--
--(1 row(s) affected)

datetimeoffset(n)

La fecha se establece '1900-01-01', y se copia la hora. El ajuste de zona horaria se establece en +00:00. Cuando la precisión de las fracciones de segundo del valor de time(n) es mayor que la precisión del valor de datetimeoffset(n), el valor se trunca para ajustarse.

El ejemplo siguiente muestra los resultados de convertir un valor de time(4) en un tipo datetimeoffset(3).

DECLARE @time time(4)
DECLARE @datetimeoffset datetimeoffset(3)
SELECT @time = '12:15:04.1234'
SELECT @datetimeoffset = @time
SELECT @time AS '@time', @datetimeoffset AS '@datetimeoffset'
--Result
--@time         @datetimeoffset
--------------- ------------------------------
--12:15:04.1234 1900-01-01 12:15:04.123 +00:00
--
--(1 row(s) affected)

datetime2(n)

La fecha se establece en '1900-01-01', se copia el componente de hora, y el ajuste de zona horaria se establece en 00:00. Cuando la precisión de las fracciones de segundo del valor de datetime2(n) es mayor que el valor de time(n), el valor se truncará para ajustarse.

El ejemplo siguiente muestra los resultados de convertir un valor de time(4) en un valor de datetime2(2).

DECLARE @time time(4)
DECLARE @datetime2 datetime2(3)
SELECT @time = '12:15:04.1234'
SELECT @datetime2 = @time
SELECT @datetime2 AS '@datetime2', @time AS '@time'
--Result
--@datetime2              @time
------------------------- -------------
--1900-01-01 12:15:04.123 12:15:04.1234
--
--(1 row(s) affected)

Tipo de datos datetime

La tabla siguiente describe lo que sucede cuando un tipo de datos datetime se convierte a otros tipos de datos de fecha y hora.

Tipo de datos al que se convierte

Detalles de la conversión

date

Se copian los valores de año, mes y día. El componente de hora se establece en 00:00:00.000.

El código siguiente muestra los resultados de convertir un valor de date en un valor de datetime.

DECLARE @date date
DECLARE @datetime datetime
SELECT @date = '12-21-05'
SELECT @datetime = @date
SELECT @datetime AS '@datetime', @date AS '@date'
--Result
--@datetime               @date
------------------------- ----------
--2005-12-21 00:00:00.000 2005-12-21

time(n)

Se copia el componente de hora, y el componente de fecha se establece en '1900-01-01'. Cuando la precisión de las fracciones del valor de time(n) es superior a tres dígitos, el valor se truncará para ajustarse.

El ejemplo siguiente muestra los resultados de convertir un valor de time(4) en un valor de datetime.

DECLARE @time time(4)
DECLARE @datetime datetime
SELECT @time = '12:10:05.1234'
SELECT @datetime = @time
SELECT @datetime AS '@datetime', @time AS '@time'
--Result
--@datetime               @time
------------------------- -------------
--1900-01-01 12:10:05.123 12:10:05.1234
--
--(1 row(s) affected)

smalldatetime

Se copian los valores de hora y minuto. Los segundos y las fracciones de segundo se establecen en 0.

El código siguiente muestra los resultados de convertir un valor de smalldatetime en un valor de datetime.

DECLARE @smalldatetime smalldatetime
DECLARE @datetime datetime
SELECT @smalldatetime = '12-01-01 12:32'
SELECT @datetime = @smalldatetime
SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime'
--Result
--@datetime               @smalldatetime
------------------------- -----------------------
--2001-12-01 12:32:00.000 2001-12-01 12:32:00
--
--(1 row(s) affected)

datetimeoffset(n)

Se copian los componentes de fecha y hora. Se trunca la zona horaria. Cuando la precisión de las fracciones del valor de datetimeoffset(n) es superior a tres dígitos, el valor se truncará.

El ejemplo siguiente muestra los resultados de convertir un valor de datetimeoffset(4) en un valor de datetime.

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @datetime datetime
SELECT @datetimeoffset = '1968-10-23 12:45:37.1234 +10:0'
SELECT @datetime = @datetimeoffset
SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset'
--Result
--@datetime               @datetimeoffset
------------------------- ------------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1234 +01:0
--
--(1 row(s) affected)

datetime2(n)

Se copian los valores de fecha y hora. Cuando la precisión de las fracciones del valor de datetime2(n) es superior a tres dígitos, el valor se truncará.

El ejemplo siguiente muestra los resultados de convertir un valor de datetime2(4) en un valor de datetime.

DECLARE @datetime2 datetime2(4)
DECLARE @datetime datetime
SELECT @datetime2 = '1968-10-23 12:45:37.1237'
SELECT @datetime = @datetime2
SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2'
--Result
--@datetime               @datetime2
------------------------- ------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237
--
--(1 row(s) affected)

Tipo de datos smalldatetime

La tabla siguiente describe lo que sucede cuando un tipo de datos smalldatetime se convierte a otros tipos de datos de fecha y hora.

Tipo de datos al que se convierte

Detalles de la conversión

date

Se copian los valores de año, mes y día.

El código siguiente muestra los resultados de convertir un valor de smalldatetime en un valor de date.

DECLARE @smalldatetime smalldatetime
DECLARE @date date
SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @date = @smalldatetime
SELECT @smalldatetime AS '@smalldatetime', @date AS 'date'
--Result
--@smalldatetime          date
------------------------- ----------
--1955-12-13 12:43:00     1955-12-13
--
--(1 row(s) affected)

time(n)

Se copian los valores de hora, minuto y segundo. Las fracciones de segundo se establecen en 0.

El código siguiente muestra los resultados de convertir un valor de smalldatetime en un valor de time(4).

DECLARE @smalldatetime smalldatetime
DECLARE @time time(4)
SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @time = @smalldatetime
SELECT @smalldatetime AS '@smalldatetime', @time AS 'time'
--Result
--@smalldatetime          time
------------------------- -------------
--1955-12-13 12:43:00     12:43:00.0000
--
--(1 row(s) affected)

datetime

El valor de smalldatetime se copia en el valor de datetime. Las fracciones de segundo se establecen en 0.

El código siguiente muestra los resultados de convertir un valor de smalldatetime en un valor de datetime.

DECLARE @smalldatetime smalldatetime
DECLARE @datetime datetime
SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @datetime = @smalldatetime
SELECT @smalldatetime AS '@smalldatetime', @datetime AS 'datetime'
--Result
--@smalldatetime          datetime
------------------------- -----------------------
--1955-12-13 12:43:00     1955-12-13 12:43:00.000
--
--(1 row(s) affected)

datetimeoffset(n)

El valor de smalldatetime se copia en el valor de datetimeoffset(n). Las fracciones de segundo se establecen en 0 y el ajuste de zona horaria se establece en +00: 0.

El código siguiente muestra los resultados de convertir un valor de smalldatetime en un valor de datetimeoffset(4).

DECLARE @smalldatetime smalldatetime
DECLARE @datetimeoffset datetimeoffset(4)
SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @datetimeoffset = @smalldatetime
SELECT @smalldatetime AS '@smalldatetime', @datetimeoffset AS 'datetimeoffset(4)'
--Result
--@smalldatetime          datetimeoffset(4)
------------------------- ------------------------------
--1955-12-13 12:43:00     1955-12-13 12:43:00.0000 +00:0
--
--(1 row(s) affected)

datetime2(n)

El valor de smalldatetime se copia en el valor de datetime2(n). Las fracciones de segundo se establecen en 0.

El código siguiente muestra los resultados de convertir un valor de smalldatetime en un valor de datetime2(4).

DECLARE @smalldatetime smalldatetime
DECLARE @datetime2 datetime2(4)
SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @datetime2 = @smalldatetime
SELECT @smalldatetime AS '@smalldatetime', @datetime2 AS ' datetime2(4)'
--Result
--@smalldatetime           datetime2(4)
------------------------- ------------------------
--1955-12-13 12:43:00     1955-12-13 12:43:00.0000
--
--(1 row(s) affected)

Tipo de datos datetimeoffset(n)

La tabla siguiente describe lo que sucede cuando un tipo de datos datetimeoffset(n) se convierte a otros tipos de datos de fecha y hora.

Tipo de datos al que se convierte

Detalles de la conversión

date

Se copian los valores de año, mes y día.

El código siguiente muestra los resultados de convertir un valor de datetimeoffset(4) en un valor de date.

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @date date
SELECT @datetimeoffset = '12-10-25 12:32:10 +01:0'
SELECT @date = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date'
--Result
--@datetimeoffset                date
-------------------------------- ----------
--2025-12-10 12:32:10.0000 +01:0 2025-12-10
--
--(1 row(s) affected)

time(n)

Se copian los valores de hora, minuto, segundo y fracciones de segundo. Se trunca el valor de zona horaria. Cuando la precisión del valor de datetimeoffset(n) es mayor que la precisión del valor de time(n), el valor se trunca.

El código siguiente muestra los resultados de convertir un valor de datetimeoffset(4) en un valor de time(3).

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @time time(3)
SELECT @datetimeoffset = '12-10-25 12:32:10.1234 +01:0'
SELECT @time = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time'
--Result
--@datetimeoffset                time
-------------------------------- ------------
--2025-12-10 12:32:10.1234 +01:0 12:32:10.123
--
--(1 row(s) affected)

datetime

Se copian los valores de fecha y hora, y se trunca la zona horaria. Cuando la precisión de las fracciones del valor de datetimeoffset(n) es superior a tres dígitos, el valor se trunca.

El código siguiente muestra los resultados de convertir un valor de datetimeoffset(4) en un valor de datetime.

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @datetime datetime
SELECT @datetimeoffset = '12-10-25 12:32:10.1234 +01:0'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
--Result
--@datetimeoffset                datetime
-------------------------------- -----------------------
--2025-12-10 12:32:10.1234 +01:0 2025-12-10 12:32:10.123
--
--(1 row(s) affected)

smalldatetime

Se copian los valores de fecha, hora y minuto. Los segundos se establecen en 0.

El código siguiente muestra los resultados de convertir un valor de datetimeoffset(3) en un valor de smalldatetime.

DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @smalldatetime smalldatetime
SELECT @datetimeoffset = '1912-10-25 12:24:32 +10:0'
SELECT @smalldatetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime'
--Result
--@datetimeoffset                @smalldatetime
-------------------------------- -----------------------
--1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00
--
--(1 row(s) affected)

datetime2(n)

La fecha y la hora se copian en el valor de datetime2 y se trunca la zona horaria. Cuando la precisión del valor de datetime2(n) es mayor que la precisión del valor de datetimeoffset(n), el valor de las fracciones de segundo se trunca para ajustarse.

El código siguiente muestra los resultados de convertir un valor de datetimeoffset(4) en un valor de datetime2(3).

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @datetime2 datetime2(3)
SELECT @datetimeoffset = '1912-10-25 12:24:32.1234 +10:0'
SELECT @datetime2 = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2'
--Result
--@datetimeoffset                @datetime2
-------------------------------- -----------------------
--1912-10-25 12:24:32.1234 +10:0 1912-10-25 12:24:32.123
--
--(1 row(s) affected)

Tipo de datos datetime2

La tabla siguiente describe lo que sucede cuando un tipo de datos datetime2 se convierte a otros tipos de datos de fecha y hora.

Tipo de datos al que se convierte

Detalles de la conversión

date

Se copian los valores de año, mes y día.

El código siguiente muestra los resultados de convertir un valor de datetime2(4) en un valor de date.

DECLARE @datetime2 datetime2(4)
DECLARE @date date
SELECT @datetime2 = '12-10-25 12:32:10.1234'
SELECT @date = @datetime2
SELECT @datetime2 AS '@datetime2', @date AS 'date'
--Result
--@datetime2               date
-------------------------- ----------
--2025-12-10 12:32:10.1234 2025-12-10
--
--(1 row(s) affected)

time(n)

Se copian los valores de hora, minuto, segundo y fracciones de segundo.

El código siguiente muestra los resultados de convertir un valor de datetime2(4) en un valor de time(3).

DECLARE @datetime2 datetime2(4)
DECLARE @time time(3)
SELECT @datetime2 = '12-10-25 12:32:10.1234'
SELECT @time = @datetime2
SELECT @datetime2 AS '@datetime2', @time AS 'time(3)'
--Result
--@datetime2                time(3) 
-------------------------- ------------
--2025-12-10 12:32:10.1234 12:32:10.123
--
--(1 row(s) affected)

datetime

Se copian los valores de fecha y hora. Cuando la precisión de las fracciones del valor de datetimeoffset(n) es superior a tres dígitos, el valor se trunca.

El código siguiente muestra los resultados de convertir un valor de datetime2 en un valor de datetime.

DECLARE @datetime2 datetime2
DECLARE @datetime datetime
SELECT @datetime2 = '12-10-25 12:32:10.1234567'
SELECT @datetime = @datetime2
SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime'
--Result
--@datetime2                  @datetime
----------------------------- -----------------------
--2025-12-10 12:32:10.1234567 2025-12-10 12:32:10.123
--
--(1 row(s) affected)

smalldatetime

Se copian los valores de fecha, hora y minuto. Los segundos se establecen en 0.

El código siguiente muestra los resultados de convertir un valor de datetime2 en un valor de smalldatetime.

DECLARE @datetime2 datetime2
DECLARE @smalldatetime smalldatetime
SELECT @datetime2 = '12-10-25 12:32:10.1234567'
SELECT @smalldatetime = @datetime2
SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime'
--Result
--@datetime2                  @datetime
----------------------------- -----------------------
--2025-12-10 12:32:10.1234567 2025-12-10 12:32:10.123
--
--(1 row(s) affected)

datetimeoffset(n)

El valor de datetime2(n) se copia en el valor de datetimeoffset(n). El ajuste de zona horaria se establece en +00:0. Cuando la precisión del valor de datetime2(n) es mayor que la precisión del valor de datetimeoffset(n), el valor se trunca para ajustarse.

El código siguiente muestra los resultados de convertir un valor de datetime2(5) en un valor de datetimeoffset(3).

DECLARE @datetime2 datetime2(3)
DECLARE @datetimeoffset datetimeoffset(2)
SELECT @datetime2 = '12-10-25 12:32:10.1234567'
SELECT @datetimeoffset = @datetime2
SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset(2)'
--Result
--@datetime2              @datetimeoffset(2)
------------------------- -----------------------------
--2025-12-10 12:32:10.123 2025-12-10 12:32:10.12 +00:00
--
--(1 row(s) affected)

Usar CAST y CONVERT con hora, fecha, datetime2 y datetimeoffset

Esta sección proporciona información sobre la conversión entre tipos de datos de fecha y hora.

Conversión en datetimeoffset

  • Cuando un valor de datetimeoffset con zona horaria vaya a derivarse implícitamente desde un valor sin zona horaria (p.ej., en una operación de asignación simple), el valor sin zona horaria se trata como local y el desplazamiento de zona horaria predeterminada (00:00) actual se resta de él para obtener UTC.
  1. Siempre se anexa la zona horaria UTC (00:00) al convertir tipos de datos sin zona horaria a datetimeoffset para las conversiones siguientes:
  • datedate en datetimeoffsetdatetimeoffset

  • timetime en datetimeoffsetdatetimeoffset

  • datetime2datetime2 en datetimeoffsetdatetimeoffset

  • datetime o smalldatetime en datetimeoffset

  • Los literales de cadena con datetimedatetime válidos sin formatos de zona horaria en datetimeoffset

Conversión desde datetimeoffset

Al convertir desde datetimeoffset a los siguientes tipos sin zonas horarias, el estilo 0 (valor predeterminado) siempre indica que el valor de retorno de date, time, datetime2, datetime o smalltime está en formato local del desplazamiento de zona horaria conservada, mientras que el estilo 1 siempre indica el formato de UTC.

Cuando un valor de fecha o tiempo sin zona horaria se deriva implícitamente a una de las conversiones siguientes, el valor de datetimeoffset se trata como UTC. El desplazamiento de la zona horaria conservada se suma al valor para obtener la hora local. El resultado, sin ningún desplazamiento de zona horaria, se muestra en hora local.

  • datetimeoffsetdatetimeoffset en datedate

  • datetimeoffsetdatetimeoffset en timetime

  • datetimeoffsetdatetimeoffset en datetime2datetime2

  • datetimeoffset en datetime o smalldatetime

  • Los estilos 0 y 1 no se pueden usar para datetimeoffset en la conversión de cadenas. En su lugar, convierta en primer lugar datetimeoffset en datetime2 o en datetime y, a continuación, en varchar o char.

Si un estilo CONVERT existente contiene la parte de hora y la conversión es de datetimeoffset a cadena, se incluirá el desplazamiento de zona horaria (salvo el estilo 127). Si no desea el desplazamiento de zona horaria, puede usar en primer lugar CAST en datetime2 y, a continuación, encadenar.

Todos los estilos de fecha y hora existentes se aplicarán a cualquier datetimeoffset en la conversión de cadenas y se mantendrá el desplazamiento de zona horaria.

La parte de desplazamiento de zona horaria de cadena de entrada debería ser siempre de dígitos dobles para hh y mm; los signos + o – son obligatorios.

Información eliminada en la conversión

  • Al convertir de datetime2 o datetimeoffset a date, no hay ningún redondeo y se extrae la parte de la fecha explícitamente. Para datetimeoffset, la extracción se realiza en la fecha y hora locales, aunque no el valor UTC.

  • Para cualquier conversión implícita de datetimeoffset en date, time, datetime2, datetime o smalldatetime, la conversión se basa en el valor de fecha y hora local (en el desplazamiento de zona horaria persistente). Por ejemplo, cuando el valor de datetimeoffset(3), 2006-10-21 12:20:20.999 -8: 00, se convierte en time(3), el resultado es 12:20:20.999, no 20:20:20.999 (UTC).

Truncamiento en la conversión

  • Se permiten las conversiones de valores de tiempo de precisión más altos en valores de precisión más bajos. Los valores de precisión más alta se truncarán para ajustarse al tipo de precisión más baja.

Convertir segundos fraccionarios

Si un estilo incluye el formato de hora hh:mm:ss.mmm, el formato se volverá hh:mm:ss. [nnnnnnn] para time(n), datetime2(n) y datetimeoffset(n). El número de dígitos depende de la especificación de tipo. Si sólo desea precisión en milisegundos, convierta primero en datetime2(3) y, a continuación, en cadena.

Para los estilos 9, 109, 13, 113, 21, 121, 130 y 131, no se admite el separador de dos puntos que precede a los segundos fraccionarios (:) para los tipos time, datetime2 y datetimeoffset en la conversión de cadenas. El formato de cadena de salida con cualquiera de estos estilos se transformará en un punto (.).

Argumentos de estilo de la función CONVERT

En la siguiente tabla se enumeran ejemplos de date, time, datetime2 y valores de datetimeoffset para los argumentos de estilo de la función CONVERT. Para obtener más información acerca del estilo, consulte la sección Argumentos de CAST y CONVERT (Transact-SQL)

Estilo

Associated standard

Input/Output (3)

format

date

time(n)

datetime2(n)

datetimeoffset(n)

0 o 100 (1,2)

Predeterminado

mes dd aaaa hh:miAM (o PM)

Ene 1 2001

12:20PM

Ene 1 2001 12:20PM

Ene 1 2001 12:20PM -08:00

101

EE.UU.

mm/dd/aaaa

01/01/2001

-

01/01/2001

01/01/2001

102

ANSI

aa.mm.dd

2001.01.01

-

2001.01.01

2001.01.01

103

Británico/Francés

dd/mm/aa

01/01/2001

-

01/01/2001

01/01/2001

104

Alemán

dd.mm.aa

01.01.2001

-

01.01.2001

01.01.2001

105

Italiano

dd-mm-aa

01-01-2001

-

01-01-2001

01-01-2001

106(1)

-

dd mes aa

01 Ene 2001

-

01 Ene 2001

01 Ene 2001

107(1)

-

Mes dd, aa

Ene 01, 2001

-

Ene 01, 2001

Ene 01, 2001

108

-

hh:mi:ss

-

12:20:20

12:20:20

12:20:20

9 o 109 (1,2)

Valor predeterminado + milisegundos

mes dd aaaa hh:mi:ss:mmma.m. (o p. m.)

Ene 1 2001

12:20:20.1234567AM

Ene 1 2001 12:20:20.1234567PM

Ene 1 2001 12:20:20:1230000PM -08:00

110

Estados Unidos

mm-dd-aa

01-01-2001

-

01-01-2001

01-01-2001

111

JAPÓN

aa/mm/dd

2001/01/01

-

2001/01/01

2001/01/01

112

ISO

aammdd

20010101

-

20010101

20010101

13 o 113 (1,2)

Europeo predeterminado + milisegundos

dd mes aaaa hh:mi:ss:mmm(24h)

01 Ene 2001

12:20:20.1234567

01 Ene 2001 12:20:20.1234567

01 Ene 2001 12:20:20:1230000 -08:00

114

-

hh:mi:ss:mmm(24h)

-

12:20:20.1234567

12:20:20.1234567

12:20:20:1230000 -08:00

20 o 120 (2)

ODBC canónico

aaaa-mm-dd hh:mi:ss(24h)

2001-01-01

12:20:20

2001-01-01 12:20:20

2001-01-01 12:20:20 -08:00

21 o 121 (2)

ODBC canónico (con milisegundos)

aaaa-mm-dd hh:mi:ss.mmm(24h)

2001-01-01

12:20:20.1234567

2001-01-01 12:20:20.1234567

2001-01-01 12:20:20.1230000 -08:00

126 (4)

ISO8601

aaaa-mm-ddThh:mi:ss.mmm (sin espacios)

2001-01-01

12:20:20.1234567

2001-01-01T 12:20:20.1234567

2001-01-01T 12:20:20.1234567

127(6, 7)

ISO8601 con zona horaria Z.

aaaa-mm-ddThh:mi:ss.mmmZ

(sin espacios)

2001-01-01

12:20:20.1234567Z

2001-01-01T 12:20:20.1234567Z

2001-01-01T20:20:20.1230000Z

130 (1,2)

Hijri (5)

dd mes aaaa hh:mi:ss:mmma.m.

01 Ene 2001

12:20:20.1230000PM

01 Ene 2001 12:20:20.1230000PM

1 Ene 2001 12:20:20:1230000PM -08:00

131 (2)

Hijri (5)

dd/mm/aa hh:mi:ss:mmma.m.

01/01/2001

12:20:20.1230000PM

01/01/2001 12:20:20.1230000PM

01/01/2001 12:20:20.1230000PM -08:00

1 Estos valores de estilo devuelven resultados no deterministas. Contiene todos los estilos (aa) (sin el siglo) y un subconjunto de estilos (aaaa) (con el siglo).

2 Los valores predeterminados (style0 o 100, 9 o 109, 13 o 113, 20 o 120 y 21 o 121) siempre devuelven el siglo (aaaa).

3 Entrada al convertir en datetime; salida al convertir en datos de caracteres.

4 Diseñado para usarse con XML. Para convertir datos datetime o smalldatetime en datos de caracteres, el formato de salida es el descrito en la tabla anterior.

5 Hijri es un sistema de calendario con varias variaciones. SQL Server usa el algoritmo Kuwaiti.

6 Sólo se admite en la conversión de datos de caracteres a datetime o smalldatetime. Cuando se convierten datos de caracteres que representan componentes de sólo fecha o sólo hora al tipo de datos datetime o smalldatetime, el componente de hora no especificado se establece en 00:00:00.000 y el componente de fecha no especificado se establece en 1900-01-01.

7El indicador opcional de zona horaria, Z, se usa para facilitar la asignación de valores XML de tipo datetime, que contienen información de zona horaria, a valores de tipo datetime de SQL Server, que no tienen zona horaria. Z es el indicador para la zona horaria UTC-0.

Limitaciones de fecha y hora de SQL Server

En la lista siguiente, la fecha y hora hacen referencia a cualquier tipo de datos de fecha y hora que incluye una parte de fecha u hora.

  • No hay compatibilidad con el horario de verano (DST) del servidor para datetimeoffset.

  • No hay compatibilidad con el calendario juliano para la fecha.

  • No hay compatibilidad horaria de representación de 24 horas para la medianoche.

  • No hay compatibilidad horaria de 'segundo intercalado' sobre '59'.

  • No hay compatibilidad horaria de 'un nanosegundo' o más para la precisión fraccionaria de segundo.

  • No hay compatibilidad de la zona horaria para la hora.

  • No hay compatibilidad con la operación INTERVAL estándar de SQL.

Compatibilidad con versiones anteriores de los clientes de niveles inferiores

Algunos clientes de niveles inferiores no admiten los tipos de datos de time, date, datetime2 y datetimeoffset agregados en SQL Server 2008. La tabla siguiente muestra la asignación de tipo entre una instancia de nivel superior de SQL Server 2008 y los clientes de nivel inferior.

Tipo de datos de SQL Server 2008

El formato del literal de cadena predeterminado se pasó al cliente de nivel inferior

ODBC de nivel inferior

OLEDB de nivel inferior

JDBC de nivel inferior

SQLCLIENT de nivel inferior

time

hh:mm:ss[.nnnnnnn]

SQL_WVARCHAR o SQL_VARCHAR

DBTYPE_WSTR o DBTYPE_STR

Java.sql.String

Cadena o SqString

date

AAAA-MM-DD

SQL_WVARCHAR o SQL_VARCHAR

DBTYPE_WSTR o DBTYPE_STR

Java.sql.String

Cadena o SqString

datetime2

AAAA-MM-DD hh:mm:ss[.nnnnnnn]

SQL_WVARCHAR o SQL_VARCHAR

DBTYPE_WSTR o DBTYPE_STR

Java.sql.String

Cadena o SqString

datetimeoffset

AAAA-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm

SQL_WVARCHAR o SQL_VARCHAR

DBTYPE_WSTR o DBTYPE_STR

Java.sql.String

Cadena o SqString

Ejemplos

A. Comparar tipos de datos de fecha y hora

El ejemplo que se muestra a continuación compara los resultados de conversión de un literal de cadena con cada tipo de dato de fecha y hora. Intentar ejecutar CAST en un literal de cadena con una precisión en segundos fraccionaria superior a lo permitido para smalldatetime o datetime, producirá un error.

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
        'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 
        'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 
        'datetimeoffset';

Tipo de datos

Salida

Time

12:35:29.1234567

Date

2007-05-08

Smalldatetime

2007-05-08 12:35:00

Datetime

2007-05-08 12:35:29.123

datetime2(7)

2007-05-08 12:35:29.1234567

datetimeoffset(7)

2007-05-08 12:35:29.1234567 +12:15

B. Obtener la fecha y hora actuales del sistema

El ejemplo siguiente muestra cómo se usan las funciones de sistema de SQL Server que devuelven fecha y hora actuales.

SELECT SYSDATETIME() AS 'SYSDATETIME'

--Results
--SYSDATETIME
--2007-10-22 14:10:41.7984554
--(1 row(s) affected)

SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'

--Results
--SYSDATETIMEOFFSET
--2007-10-22 14:11:34.2607172 -0
--(1 row(s) affected)

SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'

--Results
--SYSUTCDATETIME
--2007-10-22 21:12:11.7069470
--(1 row(s) affected)

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'

--Results
--CURRENT_TIMESTAMP
-------------------------
--2007-10-22 14:12:33.320
--(1 row(s) affected)

SELECT GETDATE() AS 'GETDATE'

--Results
--GETDATE
--2007-10-22 14:13:57.943
--(1 row(s) affected)

SELECT GETUTCDATE() AS 'GETUTCDATE'

--Results
--GETUTCDATE
--2007-10-22 21:14:35.657
--(1 row(s) affected)

C. Buscar todos los valores de datetime2 en un día

  • El ejemplo siguiente muestra cómo buscar todos los valores de fecha y hora en un día.
-- Create a table that contains with the following dates:
--     The last date-time in 2005-04-06, '2005-04-06 23:59:59.9999999'
--     The first date-time in 2005-04-07, '2005-04-07 00:00:00.0000000'
--     The last date-time in 2005-04-07, '2005-04-07 23:59:59.9999999'
--     The first date-time in 2005-04-08, '2005-04-08 00:00:00.0000000'
CREATE TABLE #Search
    (
    MyDate datetime2
    );
INSERT INTO #Search(MyDate)VALUES('2005-04-06 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 00:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-08 00:00:00.0000000');

-- The following four SELECT statements show different ways to find
-- only the two rows that contain 2005-04-07 dates.
--Use CONVERT.
SELECT MyDate 
FROM #Search 
WHERE CONVERT(date,MyDate) = '2005-04-07';

--Use >= and <=.
SELECT MyDate 
FROM #Search 
WHERE MyDate >= '2005-04-07 00:00:00.0000000' 
    AND MyDate <= '2005-04-07 23:59:59.9999999';

--Use > and <.
SELECT MyDate
FROM #Search
WHERE MyDate > '2005-04-06 23:59:59.9999999'
    AND MyDate < '2005-04-08 00:00:00.0000000';

--Use BETWEEN AND.
SELECT MyDate
FROM #Search
WHERE MyDate BETWEEN '2005-04-07 00:00:00.0000000'
    AND '2005-04-07 23:59:59.9999999';
DROP TABLE #Search
GO

D. Buscar un período de tiempo en un día

Los ejemplos siguientes muestran cómo buscar fechas que tienen valores de hora para buscar un intervalo de tiempo.

-- Create a table called Search and insert
-- five different time values for the same
-- date.

CREATE TABLE #Search
    (
    MyDate datetime2
    );

INSERT INTO #Search(MyDate)VALUES('2005-04-06 08:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 16:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 17:00:00.0000000');

-- The following SELECT statements show different ways
-- to search for dates that have time values to find a
-- time range.

--Using CONVERT with time (0) to ignore fractional seconds
SELECT MyDate 
FROM #Search 
WHERE CONVERT(time(0),MyDate) = '09:00:00';

--Returns two rows (ignores fractional seconds):
--2005-04-06 08:59:59.9999999
--2005-04-06 09:00:00.0000000

--Using CONVERT with time (7) to include fractional seconds
SELECT MyDate 
FROM #Search 
WHERE CONVERT(time(7),MyDate) = '09:00:00';

--Returns one row (matches fractional seconds):
--2005-04-06 09:00:00.0000000


--Each of the SELECT statements below use CONVERT
--to find all times in an eight-hour period.

--Use CONVERT with >= and <=.
SELECT MyDate 
FROM #Search 
WHERE CONVERT(time(7),MyDate) >=  '09:00:00.0000000'
    AND CONVERT(time(7),MyDate) <= '16:59:59.9999999'

--Use CONVERT with > and <.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) > '08:59:59.9999999'
    AND CONVERT(time(7),MyDate) < '17:00:00.0000000';

--Use CONVERT with BETWEEN AND
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) BETWEEN '09:00:00.0000000'
    AND '16:59:59.9999999';
DROP TABLE #Search
GO

E. Usar DATEPART y DATEADD para buscar los días primero y último de una parte de fecha

El ejemplo siguiente muestra cómo devolver el primer o el último día de datepart.

-- When several months, quarters, or years are added to the base
-- year,1900-01-01, the returned day is the first day of a month.
-- To calculate the last day of the current month, you need to
--
-- 1. Find the difference in months between today and the base
--    year (0 ='1900-01-01'). This is accomplished by
--
-- DATEDIFF(month, 0, SYSDATETIME())
-- 2. Add that number of months plus 1 to the base year (0)
--    to obtain the first day of the next month.
--    DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0)
-- 3. Subtract 1 day.
--
--Find the first day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()), 0);

--Find the last day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0) - 1;

-- Find the first day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, 0, SYSDATETIME()), 0);

-- Find the last day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, -1, SYSDATETIME()), -1);

-- Find the first day of the current year.
SELECT DATEADD(year, DATEDIFF(year, 0, SYSDATETIME()), 0);

-- Find the last day of the current year.
SELECT DATEADD(year, DATEDIFF(year, -1, SYSDATETIME()), -1);

F. Usar los argumentos de una parte de fecha definidos por el usuario para DATEADD, DATEDIFF, DATENAME y DATEPART

En el ejemplo siguiente se crea una función escalar definida por el usuario que agrega una constante a cualquier parte de un valor de datetime2.

USE tempdb
GO
CREATE FUNCTION UserDateadd
    (
    @datepart nvarchar(11)
    ,@number int 
    ,@date datetime2
    )
RETURNS datetime2
AS
BEGIN
    DECLARE @Return datetime2

    SELECT @Return = CASE @datepart
        WHEN 'year' THEN DATEADD(year,@number,@date) 
        WHEN 'yy' THEN DATEADD(year,@number,@date) 
        WHEN 'yyyy' THEN DATEADD(year,@number,@date) 
        WHEN 'quarter' THEN DATEADD(quarter,@number,@date) 
        WHEN 'qq' THEN DATEADD(quarter,@number,@date) 
        WHEN 'month' THEN DATEADD(month,@number,@date) 
        WHEN 'mm' THEN DATEADD(month,@number,@date) 
        WHEN 'm' THEN DATEADD(month,@number,@date) 
        WHEN 'dayofyear' THEN DATEADD(dayofyear,@number,@date) 
        WHEN 'dy' THEN DATEADD(dayofyear,@number,@date) 
        WHEN 'y' THEN DATEADD(dayofyear,@number,@date) 
        WHEN 'day' THEN DATEADD(day,@number,@date) 
        WHEN 'dd' THEN DATEADD(day,@number,@date) 
        WHEN 'd' THEN DATEADD(day,@number,@date) 
        WHEN 'week' THEN DATEADD(week,@number,@date) 
        WHEN 'wk' THEN DATEADD(week,@number,@date) 
        WHEN 'ww' THEN DATEADD(week,@number,@date) 
        WHEN 'weekday' THEN DATEADD(weekday,@number,@date) 
        WHEN 'wk' THEN DATEADD(weekday,@number,@date) 
        WHEN 'w' THEN DATEADD(weekday,@number,@date) 
        WHEN 'hour' THEN DATEADD(hour,@number,@date) 
        WHEN 'hh' THEN DATEADD(hour,@number,@date) 
        WHEN 'minute' THEN DATEADD(minute,@number,@date)
        WHEN 'mi' THEN DATEADD(minute,@number,@date) 
        WHEN 'n' THEN DATEADD(minute,@number,@date)  
        WHEN 'second' THEN DATEADD(second,@number,@date) 
        WHEN 'ss' THEN DATEADD(second,@number,@date)
        WHEN 's' THEN DATEADD(second,@number,@date)
        WHEN 'millisecond' THEN DATEADD(millisecond,@number,@date) 
        WHEN 'ms' THEN DATEADD(millisecond,@number,@date) 
        WHEN 'microsecond' THEN DATEADD(microsecond,@number,@date)
        WHEN 'mcs' THEN DATEADD(microsecond,@number,@date)
        WHEN 'nanosecond' THEN DATEADD(nanosecond,@number,@date)
        WHEN 'ns' THEN DATEADD(nanosecond,@number,@date)
    END
    return @Return
END
GO

SELECT GetDate()
SELECT dbo.UserDateadd('year', 1, GetDate())
GO

H. Usar DATEPART para agrupar por partes de una fecha

En el ejemplo siguiente se usa la base de datos de ejemplo AdventureWorks. DATEPART se usa para agrupar las ventas totales por día de la semana, mes, año y año/mes/día de la semana.

USE AdventureWorks
GO
SELECT SUM(TotalDue) AS 'Total Sales'
    ,DATEPART(year,OrderDate)AS 'By Year'
    ,DATEPART(month,OrderDate) AS 'By Month'
    ,DATEPART(weekday,OrderDate) AS 'By Weekday'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) IN('2003','2004')
GROUP BY GROUPING SETS 
    (
    (DATEPART(year,OrderDate))
    ,(DATEPART(month,OrderDate))
    ,(DATEPART(weekday,OrderDate))
    ,(
        DATEPART(year,OrderDate)
        ,DATEPART(month,OrderDate),
        DATEPART(weekday,OrderDate))
    )
ORDER BY DATEPART(year,OrderDate)
    ,DATEPART(month,OrderDate)
    ,DATEPART(weekday,OrderDate)