{"id":2384,"date":"2020-12-28T16:08:34","date_gmt":"2020-12-28T16:08:34","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2384"},"modified":"2021-11-02T12:49:48","modified_gmt":"2021-11-02T12:49:48","slug":"trabajando-con-fechas-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2384","title":{"rendered":"Trabajando con fechas en SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas__D.png\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas__D.png\" alt=\"\" class=\"wp-image-2385\"\/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Manejando Fechas en SQL Server<\/h2>\n\n\n\n<p>Las funciones de fecha y hora de SQL Server permiten manejar y hacer los c\u00e1lculos usando campos de tipo Fecha y de tipo Hora, es posible que para efectos de alg\u00fan proceso se tengan que calcular la cantidad de a\u00f1os, meses o d\u00edas que existen entre dos fechas determinadas. En este art\u00edculo se explica como manejar los datos de tipos fecha para realizar estos c\u00e1lculos.<\/p>\n\n\n\n<!--more-->\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Para m\u00e1s informaci\u00f3n ver:<br><\/strong><a href=\"https:\/\/manualsqlserver.com\/?p=185\" target=\"_blank\" rel=\"noreferrer noopener\">Funciones de fecha y hora<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=192\" target=\"_blank\" rel=\"noreferrer noopener\">Listado usando Select<\/a><\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Usando la base de datos Northwind<\/strong><\/p>\n\n\n\n<p>use Northwind<br>go<\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Calculando edades, la cantidad de a\u00f1os, meses y d\u00edas de un empleado.  <\/strong><\/p>\n\n\n\n<p><strong>Para el c\u00e1lculo de a\u00f1os, meses y d\u00edas se ha usado DateDiff, luego a la fecha de nacimiento se ha agregado el resultado de DateDiff, si la fecha es superior a la fecha actual, se le resta UNO (uso de IIF), en otro caso, el resultado es la diferencia obtenida.<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\" style=\"font-size:12px\"><code>select \n\tE.LastName + space(1) + E.FirstName As 'Empleado',\n\tFormat(E.BirthDate, 'dd\/MM\/yyyy') As 'Fecha de nacimiento',\n\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\tDatediff(YEAR, E.BirthDate,GetDate())) As 'A\u00f1os', \n\tiif (\n\t\tDateAdd (\n\t\t\tMONTH,\n\t\t\tDateDiff(MONTH,\n\t\t\t\tDateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\n\t\t\t\t\tE.BirthDate)\n\t\t\t, GetDate()), --  Cantidad de meses \n\t\t\tDateadd(\n\t\t\tYEAR,\n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\t\n\t\t\tE.BirthDate)) &gt; GetDate(),\n\t\t\tDateDiff(MONTH,\n\t\t\t\tDateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\n\t\t\t\t\tE.BirthDate)\n\t\t\t, GetDate()) -1,\n\t\t\tDateDiff(MONTH,\n\t\t\t\tDateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\n\t\t\t\t\tE.BirthDate)\n\t\t\t, GetDate()))\n\t\tAs 'Meses', \n\t  DateDiff(DAY,\n\t\t\t(DateAdd ( MONTH, iif ( DateAdd ( MONTH, DateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),E.BirthDate) , GetDate()), --  Cantidad de meses \n\t\t\tDateadd( YEAR, \n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\t E.BirthDate)) &gt; GetDate(),\n\t\t\tDateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())), E.BirthDate) , GetDate()) -1,\n\t\t\tDateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())), E.BirthDate) , GetDate())) ,\n\t\t\t( DateAdd(YEAR,\n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())) ,E.BirthDate) )  )), GetDate())\n\t As 'D\u00edas'\t\t\nfrom Employees As E\ngo<\/code><\/pre>\n\n\n\n<p>El resultado se muestra en la siguiente imagen (fecha de captura de la imagen 28\/12\/2020)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_01.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_01.png\" alt=\"\" class=\"wp-image-2386\" width=\"750\" height=\"439\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_01.png 571w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_01-300x175.png 300w\" sizes=\"auto, (max-width: 750px) 100vw, 750px\" \/><\/a><figcaption>A\u00f1os, meses y d\u00edas exactos al 28 de diciembre de 2020<\/figcaption><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Mostrar el cumplea\u00f1os del a\u00f1o actual<\/strong><\/p>\n\n\n\n<p>Select<br>E.LastName + space(1) + E.FirstName As &#8216;Empleado&#8217;,<br>Format(E.BirthDate, &#8216;dd\/MM\/yyyy&#8217;) As &#8216;Fecha de nacimiento&#8217;,<br>Format(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate), &#8216;dd\/MM\/yyyy&#8217;)<br>As &#8216;Cumplea\u00f1o a\u00f1o actual&#8217;<br>from Employees As E<br>go<\/p>\n\n\n\n<p><br>El resultado se muestra en la siguiente imagen. (fecha de captura de la imagen 28\/12\/2020)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_02.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_02.png\" alt=\"\" class=\"wp-image-2387\" width=\"714\" height=\"400\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_02.png 569w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_02-300x168.png 300w\" sizes=\"auto, (max-width: 714px) 100vw, 714px\" \/><\/a><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>En este ejercicio se muestra la fecha de nacimiento, el d\u00eda del cumplea\u00f1os del a\u00f1o actual, los meses transcurridos despu\u00e9s del cumplea\u00f1os del a\u00f1o actual, la fecha del \u00faltimo mes cumplido y los d\u00edas transcurridos desde el \u00faltimo mes cumplido.<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\" style=\"font-size:12px\"><code>select \n\t\tE.LastName + space(1) + E.FirstName As 'Empleado',\n\t\tFormat(E.BirthDate, 'dd\/MM\/yyyy') As 'Fecha de nacimiento',\n\t\tFormat( DATEADD(yy,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)\n\t\t, 'dd\/MM\/yyyy') As 'Cumple a\u00f1o actual',\n\t\t-- Meses transcurridos\n\t\tiif (\n\t\tDateAdd (\n\t\t\tMONTH,\n\t\t\tDateDiff(MONTH,\n\t\t\t\tDateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\n\t\t\t\t\tE.BirthDate)\n\t\t\t, GetDate()), --  Cantidad de meses \n\t\t\tDateadd(\n\t\t\tYEAR,\n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\t\n\t\t\tE.BirthDate)) &gt; GetDate(),\n\t\t\tDateDiff(MONTH,\n\t\t\t\tDateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\n\t\t\t\t\tE.BirthDate)\n\t\t\t, GetDate()) -1,\n\t\t\tDateDiff(MONTH,\n\t\t\t\tDateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\n\t\t\t\t\tE.BirthDate)\n\t\t\t, GetDate()))\n\t\tAs 'Meses',\n\t\tFormat(\n\t\t\t(DateAdd ( MONTH, iif ( DateAdd ( MONTH, DateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),E.BirthDate) , GetDate()), --  Cantidad de meses \n\t\t\tDateadd( YEAR, \n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\t E.BirthDate)) &gt; GetDate(),\n\t\t\tDateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())), E.BirthDate) , GetDate()) -1,\n\t\t\tDateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())), E.BirthDate) , GetDate())) ,\n\t\t\t( DateAdd(YEAR,\n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())) ,E.BirthDate) )  ))\n\t\t\t,'dd\/MM\/yyyy')\n\t\t\tAs '\u00daltimo mes cumplido'\n\t\t,\n\t\tDatediff\n\t\t\t(dd, \n\t\t\t(DateAdd ( MONTH, iif ( DateAdd ( MONTH, DateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),E.BirthDate) , GetDate()), --  Cantidad de meses \n\t\t\tDateadd( YEAR, \n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())),\t E.BirthDate)) &gt; GetDate(),\n\t\t\tDateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())), E.BirthDate) , GetDate()) -1,\n\t\t\tDateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())), E.BirthDate) , GetDate())) ,\n\t\t\t( DateAdd(YEAR,\n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.BirthDate,GetDate())) ,E.BirthDate) )  ))\n\t\t\t,\n\t\t\tGetDate()\n\t\t\t)\n\t\t\tAs 'D\u00edas desde el \u00faltimo mes cumplido'\n\t\tfrom Employees As E\ngo<\/code><\/pre>\n\n\n\n<p><strong><br>El resultado se muestra en la siguiente imagen. (fecha de captura de la imagen 28\/12\/2020)<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_03.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_03.png\" alt=\"\" class=\"wp-image-2388\" width=\"768\" height=\"244\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_03.png 1015w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_03-300x95.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_03-768x244.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_03-795x252.png 795w\" sizes=\"auto, (max-width: 768px) 100vw, 768px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 4<\/strong><\/p>\n\n\n\n<p><strong>Mostrar la cantidad de a\u00f1os, meses y d\u00edas en el trabajo.<br><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\" style=\"font-size:12px\"><code>select \n\tE.LastName + space(1) + E.FirstName As 'Empleado',\n\tFormat(E.HireDate, 'dd\/MM\/yyyy') As 'Fecha de contrato',\n\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\tDatediff(YEAR, E.HireDate,GetDate())) As 'A\u00f1os', \n\tiif (\n\t\tDateAdd (\n\t\t\tMONTH,\n\t\t\tDateDiff(MONTH,\n\t\t\t\tDateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())),\n\t\t\t\t\tE.HireDate)\n\t\t\t, GetDate()), --  Cantidad de meses \n\t\t\tDateadd(\n\t\t\tYEAR,\n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())),\t\n\t\t\tE.HireDate)) &gt; GetDate(),\n\t\t\tDateDiff(MONTH,\n\t\t\t\tDateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())),\n\t\t\t\t\tE.HireDate)\n\t\t\t, GetDate()) -1,\n\t\t\tDateDiff(MONTH,\n\t\t\t\tDateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())),\n\t\t\t\t\tE.HireDate)\n\t\t\t, GetDate()))\n\t\tAs 'Meses', \n\t  DateDiff(DAY,\n\t\t\t(DateAdd ( MONTH, iif ( DateAdd ( MONTH, DateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())),E.HireDate) , GetDate()), --  Cantidad de meses \n\t\t\tDateadd( YEAR, \n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())),\t E.HireDate)) &gt; GetDate(),\n\t\t\tDateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())), E.HireDate) , GetDate()) -1,\n\t\t\tDateDiff(MONTH, Dateadd(YEAR,\n\t\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\t\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())), E.HireDate) , GetDate())) ,\n\t\t\t( DateAdd(YEAR,\n\t\t\t\tiif(Dateadd(YEAR,Datediff(YEAR, E.HireDate,GetDate()),E.HireDate)&gt; GetDate(),\n\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())-1,\n\t\t\t\tDatediff(YEAR, E.HireDate,GetDate())) ,E.HireDate) )  )), GetDate())\n\t As 'D\u00edas'\t\t\nfrom Employees As E\ngo<\/code><\/pre>\n\n\n\n<p><strong>El resultado se muestra en la siguiente imagen. (fecha de captura de la imagen 28\/12\/2020)<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_04.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_04.png\" alt=\"\" class=\"wp-image-2389\" width=\"759\" height=\"444\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_04.png 551w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_04-300x175.png 300w\" sizes=\"auto, (max-width: 759px) 100vw, 759px\" \/><\/a><\/figure>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 5<\/strong><\/p>\n\n\n\n<p><strong>Mostrar las \u00f3rdenes y la cantidad de d\u00edas en ser atendidas.<br><\/strong>select<br>O.OrderID As &#8216;N\u00ba Orden&#8217;, C.CompanyName As &#8216;Cliente&#8217;,<br>Format(O.OrderDate,&#8217;dd\/MM\/yyyy&#8217;) As &#8216;Fecha Registro&#8217;,<br>Format(O.ShippedDate,&#8217;dd\/MM\/yyyy&#8217;) As &#8216;Fecha atenci\u00f3n&#8217;,<br>DATEDIFF(dd,O.OrderDate, O.ShippedDate) As &#8216;D\u00edas de demora&#8217;<br>from Orders As O<br>join Customers As C on O.CustomerID = C.CustomerID<br>where O.ShippedDate is not null<br>Order by [D\u00edas de demora] desc<br>go<br><strong>El resultado se muestra en la siguiente imagen<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_05.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_05.png\" alt=\"\" class=\"wp-image-2390\" width=\"772\" height=\"329\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_05.png 756w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_05-300x128.png 300w\" sizes=\"auto, (max-width: 772px) 100vw, 772px\" \/><\/a><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 6<\/strong><\/p>\n\n\n\n<p><strong>Listado de cumplea\u00f1os del mes.<br><\/strong>select<br>E.LastName + space(1) + E.FirstName As &#8216;Empleado&#8217;,<br>Format(E.BirthDate, &#8216;dd\/MM\/yyyy&#8217;) As &#8216;Fecha de nacimiento&#8217;,<br>Day(E.BirthDate) As &#8216;D\u00eda&#8217;<br>from Employees As E<br>where MONTH(E.BirthDate) = MONTH(GetDate())<br>order by D\u00eda<br>go<br><strong>El resultado se muestra en la siguiente imagen. (fecha de captura de la imagen 28\/12\/2020)<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_06.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_06.png\" alt=\"\" class=\"wp-image-2391\" width=\"665\" height=\"200\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_06.png 412w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_06-300x90.png 300w\" sizes=\"auto, (max-width: 665px) 100vw, 665px\" \/><\/a><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 7<\/strong><\/p>\n\n\n\n<p><strong>A\u00f1os de los empleados, ordenados de menor a mayor<br><\/strong><\/p>\n\n\n\n<p>select<br>E.LastName + space(1) + E.FirstName As &#8216;Empleado&#8217;,<br>Format(E.BirthDate, &#8216;dd\/MM\/yyyy&#8217;) As &#8216;Fecha de nacimiento&#8217;,<br>iif(Dateadd(YEAR,Datediff(YEAR, E.BirthDate,GetDate()),E.BirthDate)&gt; GetDate(),<br>Datediff(YEAR, E.BirthDate,GetDate())-1,<br>Datediff(YEAR, E.BirthDate,GetDate())) As &#8216;A\u00f1os&#8217;<br>from Employees As E<br>order by 3<br>go<\/p>\n\n\n\n<p><br><strong>El resultado se muestra en la siguiente imagen. (fecha de captura de la imagen 28\/12\/2020)<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_07.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_07.png\" alt=\"\" class=\"wp-image-2392\" width=\"680\" height=\"438\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_07.png 500w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_07-300x193.png 300w\" sizes=\"auto, (max-width: 680px) 100vw, 680px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p style=\"font-size:28px\"><strong>Funci\u00f3n GetUTCDate<\/strong><\/p>\n\n\n\n<p>La funci\u00f3n GetUTCDate muestra la hora de acuerdo al Meridiano de  Greenwich, que es el meridiano a partir del cual se miden las longitudes. El equipo donde se ha ejecutado las instrucciones se encuentra ubicado en la zona horaria (UTC-05:00) Bogot\u00e1, Lima, Quito, Rio Branco.<\/p>\n\n\n\n<p><strong>La instrucci\u00f3n siguiente muestra la fecha actual usando GetDate, el resultado de GetUTCDate y la fecha actual sum\u00e1ndole las 5 horas de diferencia seg\u00fan la zona horaria de Per\u00fa, que es la configuraci\u00f3n del equipo desde donde se publican los art\u00edculos de esta p\u00e1gina.<\/strong><\/p>\n\n\n\n<p>Select GETDATE() As &#8216;Fecha actual&#8217;,<br>GETUTCDATE() As &#8216;Fecha UTC&#8217;,<br>DateAdd(hh,5,GetDate()) As &#8216;Fecha actual mas 5 horas&#8217;<br>go<br><strong>El resultado se muestra en la siguiente imagen. (fecha de captura de la imagen 28\/12\/2020)<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_08.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_08.png\" alt=\"\" class=\"wp-image-2393\" width=\"735\" height=\"159\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_08.png 687w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_ManejandoFechas_08-300x65.png 300w\" sizes=\"auto, (max-width: 735px) 100vw, 735px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Manejando Fechas en SQL Server Las funciones de fecha y hora de SQL Server permiten manejar y hacer los c\u00e1lculos usando campos de tipo Fecha y de tipo Hora, es posible que para efectos de alg\u00fan proceso se tengan que calcular la cantidad de a\u00f1os, meses o d\u00edas que existen entre dos fechas determinadas. En &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2384\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2385,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,6],"tags":[272,273,274],"class_list":["post-2384","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-funcionessql","tag-dateadd","tag-datediff","tag-fechas-en-sql-server","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2384"}],"version-history":[{"count":3,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2384\/revisions"}],"predecessor-version":[{"id":2594,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2384\/revisions\/2594"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2385"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}