t-sql 2012 compare datetime fields

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  January 12, 2017 at 9:13 am

  #339269

  In t-sql 2012, I have 2 datetime fields where I need to compare the datetime values. I want to compare

  date1 (datetime,null) to date2(datetime,null).

  Thus can you show me the sql on how to compare date1 to date2?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 12, 2017 at 9:42 am

  #1922361

  wendy elizabeth - Thursday, January 12, 2017 9:13 AM

  In t-sql 2012, I have 2 datetime fields where I need to compare the datetime values. I want to compare

  date1 (datetime,null) to date2(datetime,null).

  Thus can you show me the sql on how to compare date1 to date2?

  If your comparison involves more than simply using the equals operator, then you will have to provide a more comprehensive explanation.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  January 12, 2017 at 9:43 am

  #1922362

  wendy elizabeth - Thursday, January 12, 2017 9:13 AM

  In t-sql 2012, I have 2 datetime fields where I need to compare the datetime values. I want to compare

  date1 (datetime,null) to date2(datetime,null).

  Thus can you show me the sql on how to compare date1 to date2?

  Do you mean something like this?

  DECLARE @TimePoint1 DATETIME = '01-Jan-2016 11:00',
@TimePoint2 DATETIME = '01-Jan-2016 11:15';

  --set @TimePoint1 = DATEADD(minute,15,@TimePoint1);
--print @timepoint1;

  IF @TimePoint1 = @TimePoint2
BEGIN
PRINT 'same';
END
ELSE
BEGIN
PRINT 'not same';
END

• whenriksen

  SSCarpal Tunnel

  Points: 4752

  More actions

  January 12, 2017 at 12:53 pm

  #1922408

  wendy elizabeth - Thursday, January 12, 2017 9:13 AM

  date1 (datetime,null) to date2(datetime,null).

  WHERE date1 = date2
OR date1 IS NULL AND date2 IS NULL

  You can't compare the nulls directly so they are put into a separate test in which both dates have to be null for it to be a match.

  Wes
  (A solid design is always preferable to a creative workaround)

• sajikv2007

  SSC Veteran

  Points: 257

  More actions

  January 16, 2017 at 3:14 am

  #1922924

  It depends on how you want to compare both the fields. If you want to compare only the date then you will have to convert or format the datetime fields. Please find below example. 

  Declare @datetime1 datetime = '2017-01-16 05:04:49',@datetime2 datetime = '2017-01-16 10:21:17'

  IF @datetime1 = @datetime2
  BEGIN
  Print'Both the dates Matches'
  END
  ELSE
  BEGIN
  Print 'Both does not Match'
  END

  IF CONVERT(Varchar,@datetime1,103) = CONVERT(varchar,@datetime2,103)
  BEGIN
  Print'Both the dates Matches'
  END
  ELSE
  BEGIN
  Print 'Both date does not Match'
  END