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
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' |
Sí |
No |
Sí |
Fecha |
Numérico |
'02/23/1998' |
Sí |
sí |
No (DATEFORMAT) |
Fecha |
ISO 8601 Numérico |
'1998-02-23' |
Sí |
No |
No |
Fecha |
Alfabético |
'23 de febrero de 1998' |
Sí |
No |
No (mes o abreviatura del mes) |
Fecha |
Fecha ODBC |
{d '1998-02-23'} |
No |
No |
Sí |
Hora |
Hora ISO 8601 |
'14:23:05' '10:00:00.123456' |
Sí |
No |
Sí |
Hora |
Hora ODBC |
{t '14:23:05'} |
No |
No |
Sí |
Fecha/hora |
Fecha y hora ODBC |
{ts '1998-02-23 14:23:05'} |
No |
No |
Sí |
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 |
Sí (parte de la fecha) |
No |
Zona horaria |
Formato de zona horaria |
'+12:00' '01:00' '-08:00' 'Z' |
Sí |
No |
Sí |
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
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.
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.
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á.
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. |
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. |
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). |
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). |
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). |
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. |
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. |
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). |
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). |
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. |
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. |
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. |
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. |
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. |
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. |
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). |
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. |
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). |
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). |
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. |
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). |
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. |
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. |
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). |
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. |
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). |
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. |
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. |
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). |
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.
- 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)
Vea también