CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Paul V
Paul V

Reputation: 7

Comparing Dates In The Same Entry

I am going through a database and am trying to single out entries in a column that do not have the same dates. Most entries in my column EntryInfo look like this;

| id |   |                        Entry Info                        |
  1       [randomInfo]<datetime1>[randomInfo]<datetime2>[randomInfo]

Example:

Received: from x400 by nccwh-3.qs-va.comm.net with local (Exim 4.66) (envelope-from <[email protected]>) id 1aSdvU-0002xZ-3k for [email protected]; Mon, 08 Feb 2016 04:59:52 +0000 X-Virus-Scanned: OK X-Spam-Threshold:95 X-Spam-Score: 0 X-Spam-Flag: NO X-MessageSniffer-Scan-Result: 0 X-MessageSniffer-Rules: 0-0-0-2110-c [some text] Orig-To: [email protected] X-Originating-Ip: [redacted] Date: 08 Feb 2016 04:59:52 +0000 Message-ID:<"15025881 0aa0 56b820c7"* @MHS>..

In most cases <datetime1> and <datetime2> have the same date. However sometimes DATE(<datetime1>) != DATE(<datetime2>). This is what I am looking for. I'm a bit new to using SQL and am unsure on how to check whether or not these dates are the same within the same entry on the table. I believe the entries are just a string.

Thanks.

Upvotes: 0

Views: 73

Answers (1)

Matt Lapka
Matt Lapka

Reputation: 66

Okay, this is going to get a bit messy -- this assumes all entries are in the same format as the example you posted in the comments. It boils to down to comparing two substrings of the entry.

Let's start with the code. This will pull all entries where the dates are different:

SELECT * FROM TestTable
WHERE (SUBSTRING(EntryInfo, PATINDEX('%COMM.COM;%', EntryInfo) + 15, PATINDEX('%+0000%', SUBSTRING(EntryInfo, PATINDEX('%COMM.COM;%', EntryInfo) + 15, PATINDEX('%+0000%', EntryInfo)))-2))
<>(SUBSTRING(EntryInfo, PATINDEX('%Date:%', EntryInfo) + 6, PATINDEX('%+0000%', SUBSTRING(EntryInfo, PATINDEX('%Date:%', EntryInfo) + 6, PATINDEX('%+0000%', EntryInfo)))-1))

This compares the two date strings pulled from the entry using SUBSTRING AND PATINDEX

SUBSTRING will find a portion of the string. It requires the source string (in the case the column name), a start index (we use PATINDEX for this) and a length.

PATINDEX will return the starting index of a pattern you supply it from the source string.

Here we pull the first date string:

(SUBSTRING(EntryInfo, PATINDEX('%COMM.COM;%', EntryInfo) + 15, PATINDEX('%+0000%', SUBSTRING(EntryInfo, PATINDEX('%COMM.COM;%', EntryInfo) + 15, PATINDEX('%+0000%', EntryInfo)))-2))

Source string is: EntryInfo

Starting index is: PATINDEX('%COMM.COM;%', EntryInfo) + 15 -- Here we are looking for that starting index of the first COMM.COM; (and adding 15 to take remove COMM.COM; and day of the week)

Length is: PATINDEX('%+0000%', SUBSTRING(EntryInfo, PATINDEX('%COMM.COM;%', EntryInfo) + 15, PATINDEX('%+0000%', EntryInfo)))-2 -- Here were are find the end index of the Date by looking for the first index of +0000 from a string that that has removed all the text before the first date. This is the SUBSTRING portion of this. We subtract 2 to remove extra spacing.

Finding the second date using similar logic, but instead uses "Date:" as an indicator for the beginning of the string.

Edit: spelling and minor logic fix.

Updated query to match updated example data.

Upvotes: 1

Related Questions