MS SQL comparar fechas?

Question

MS SQL comparar fechas?

Tengo 2 fechas (datetimes):

date1 = 2010-12-31 15:13:48.593
date2 = 2010-12-31 00:00:00.000

Es el mismo día, solo que a diferentes horas. Comparar date1 y date2 usando

Gracias:)

75

date sql-server tsql sql-server-2008

Author: Martin Smith, 2011-01-25

Source

5 answers

Utilice el DATEDIFF función con un datepart de day.

SELECT ...
FROM ...
WHERE DATEDIFF(day, date1, date2) >= 0

Tenga en cuenta que si desea probar que date1 date2 entonces necesitas probar DATEDIFF(day, date1, date2) >= 0, o alternativamente podrías probar DATEDIFF(day, date2, date1) <= 0.

61

Author: LukeH,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-01-25 13:49:13

La solución simple de una línea es

datediff(dd,'2010-12-31 15:13:48.593','2010-12-31 00:00:00.000')=0

datediff(dd,'2010-12-31 15:13:48.593','2010-12-31 00:00:00.000')<=1

datediff(dd,'2010-12-31 15:13:48.593','2010-12-31 00:00:00.000')>=1

Puede probar varias opciones con esto que no sean"dd"

3

Author: Sarathi B,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-10-31 21:32:01

Siempre se usa DateDiff(day,date1,date2) para comparar dos fechas.

Checkout siguiente ejemplo. Simplemente copie eso y ejecute en Ms sql Server. Además, intente cambiar la fecha del 31 de diciembre al 30 de diciembre y verifique el resultado

BEGIN

declare @firstDate datetime
declare @secondDate datetime


declare @chkDay int

set @firstDate ='2010-12-31 15:13:48.593'
set @secondDate ='2010-12-31 00:00:00.000'

set @chkDay=Datediff(day,@firstDate ,@secondDate )

if @chkDay=0
    Begin
        Print 'Date is Same'
    end
else
    Begin
        Print 'Date is not Same'
    end
End

0

Author: Jignesh Darji,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-05 07:06:34

Prueba Esto:

BEGIN

declare @Date1 datetime
declare @Date2 datetime

declare @chkYear int
declare @chkMonth int
declare @chkDay int
declare @chkHour int
declare @chkMinute int
declare @chkSecond int
declare @chkMiliSecond int

set @Date1='2010-12-31 15:13:48.593'
set @Date2='2010-12-31 00:00:00.000'

set @chkYear=datediff(yyyy,@Date1,@Date2)
set @chkMonth=datediff(mm,@Date1,@Date2)
set @chkDay=datediff(dd,@Date1,@Date2)
set @chkHour=datediff(hh,@Date1,@Date2)
set @chkMinute=datediff(mi,@Date1,@Date2)
set @chkSecond=datediff(ss,@Date1,@Date2)
set @chkMiliSecond=datediff(ms,@Date1,@Date2)

if @chkYear=0 AND @chkMonth=0 AND @chkDay=0 AND @chkHour=0 AND @chkMinute=0 AND @chkSecond=0 AND @chkMiliSecond=0
    Begin
        Print 'Both Date is Same'
    end
else
    Begin
        Print 'Both Date is not Same'
    end
End

-1

Author: Kumar Akhil,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-02-19 18:39:16

score 77 · Accepted Answer

SELECT CASE WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) ...

Debe hacer lo que necesita.

Caso de prueba

WITH dates(date1, date2, date3, date4)
     AS (SELECT CAST('20101231 15:13:48.593' AS DATETIME),
                CAST('20101231 00:00:00.000' AS DATETIME),
                CAST('20101231 15:13:48.593' AS DATETIME),
                CAST('20101231 00:00:00.000' AS DATETIME))
SELECT CASE
         WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) THEN 'Y'
         ELSE 'N'
       END AS COMPARISON_WITH_CAST,
       CASE
         WHEN date3 <= date4 THEN 'Y'
         ELSE 'N'
       END AS COMPARISON_WITHOUT_CAST
FROM   dates

Devuelve

COMPARISON_WITH_CAST   |  COMPARISON_WITHOUT_CAST
Y                         N