MS SQL compare dates

Question

I have 2 dates  datetimes    date1   2010-12-31 15 13 48 593 date2   2010-12-31 00 00 00 000  Its the same day  just different times  Comparing date1 and date2 using  lt   doesnt work because of the date1 time  So date1  lt   date2 is wrong  but should be true  Can I compare them by just looking at the year  month and day so they are the same  Its SQL Server 2008   Thanks

User · Answer

Use the DATEDIFF function with a datepart of day   SELECT     FROM     WHERE DATEDIFF day  date1  date2   gt   0   Note that if you want to test that date1  lt   date2 then you need to test that DATEDIFF day  date1  date2   gt   0  or alternatively you could test DATEDIFF day  date2  date1   lt   0

User · Answer

I am always used DateDiff day date1 date2  to compare two date   Checkout following example  Just copy that and run in Ms sql server  Also  try with change date by 31 dec to 30 dec and check result  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

User · Answer

The simple one line solution is   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   lt  1  datediff dd  2010-12-31 15 13 48 593   2010-12-31 00 00 00 000   gt  1   You can try various option with this other than  dd

User · Answer

Try This   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

User · Answer

SELECT CASE WHEN CAST date1 AS DATE   lt   CAST date2 AS DATE       Should do what you need  Test Case 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   lt   CAST date2 AS DATE  THEN  Y           ELSE  N         END AS COMPARISON WITH CAST         CASE          WHEN date3  lt   date4 THEN  Y           ELSE  N         END AS COMPARISON WITHOUT CAST FROM   dates   Returns COMPARISON WITH CAST      COMPARISON WITHOUT CAST Y                         N

[sql-server] MS SQL compare dates?

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to date