SQL timestamp comparison and arithmetics

Question

I am trying to query and join two tables from database by connecting to an MS SQL DB server, where the Timestamps from two tables are equal (at 'minute' or "hour" precision).

SELECT
      M.TIME,
      M.ERRORCODE,
      A.TIME,
      A.ASSAYNAME
FROM
      MESSAGES M, ASSAYS A
WHERE
      M.TIME = A.TIME

The problem is the TIME for both tables are like this format "12/07/2015 12:00:12 AM". How am I able to ask SQL only compare the time within more relaxed time frame, say at minute or at hour level.

For example Precision at minute level "12/07/2015 12:00:12 AM" will be considered equal to "12/07/2015 12:00:59 AM"

Precision at hour level "12/07/2015 12:00:12 AM" will be considered equal to "12/07/2015 12:45:59 AM"

Precision at 10 minutes "12/07/2015 12:00:12 AM" will be considered equal to "12/07/2015 12:09:59 AM"

I think another way to ask this question is "Are there ways to evaluate two timestamps by arithmetic" like M.TIME - A.TIME < 10 minutes

I guess this topic would help you: http://stackoverflow.com/questions/11617962/calculating-difference-between-two-timestamps-in-oracle-in-milliseconds — BenBen, May 19 '16 at 17:49

user212514 · Accepted Answer · 2016-05-19T18:56:17.393

2

In Microsoft SQL Server you can see whether the two datetime values are within 10 minutes of each other like this:

WHERE ABS(DATEDIFF(minute, M.TIME, A.TIME)) < 10

Or within 10 hours of each other like this:

WHERE ABS(DATEDIFF(hour, M.TIME, A.TIME)) < 10

edited May 19 '16 at 18:56

answered May 19 '16 at 18:12

user212514

3,110
1
15
11

For "within 10 hours of each other like this: WHERE ABS(DATEDIFF(minute, M.TIME, A.TIME)) < 10". Do you mean DATEDIFF(hour, M.TIME, A.TIME) – ju. May 19 '16 at 18:55
I tried this, but it complained DATEDIFF is not recognizable identifier. I guess it is because that I am connecting to a Microsoft SQL server, which I forgot to mention. I apologize. – ju. May 20 '16 at 19:27
I'm certain `DATEDIFF` works on Microsoft SQL Server. Try this for example: `SELECT DATEDIFF(hour, '5/22/2016', '5/23/2016')`. – user212514 May 20 '16 at 21:43

score 1 · Answer 2 · answered May 19 '16 at 18:14

1

I would urge you to reconsider your intentions when trying to join two tables on a date_time column type, but if you must...

(SQL Server based examples)

You simply need to cast both columns to a common format before comparing.

SELECT
      M.TIME,
      M.ERRORCODE,
      A.TIME,
      A.ASSAYNAME
FROM  MESSAGES M, ASSAYS A
WHERE CONVERT(VARCHAR, M.TIME, 101) = SELECT CONVERT (VARCHAR, A.TIME, 20)

Use DATEDIFF or DATEADD if you want to compare if a value falls into a range.

answered May 19 '16 at 18:14

Ricardo C

2,205
20
24

Thank you for your answer, but both TIME values from table A and table M are in the same format. I was asking about how to do the comparison at different precision level though. – ju. May 20 '16 at 16:36
@ju no Ju, they are the same TYPE but format is not the same as TYPE. The function CONVERT will format the date-time values to the format of your choice (say, one with no milliseconds), and it returns a value of TYPE Varchar(), then is the resulting varchars (with no milliseconds) used in the comparison. – Ricardo C May 20 '16 at 17:06

SQL timestamp comparison and arithmetics

2 Answers2