Date Comparisons in SQL

Topic

New Reply

I’m trying to prevent duplicates being created in a table when I import outlook messages.

My logic is to compare the send and time the message was sent to determine if I already have the record.

Code:

strSQL = "SELECT * FROM tblOutlookMessage WHERE Message_From = """ & msg.SenderName & """ AND Message_Sent = #" & msg.SentOn & "#"

Comparing the two dates fails to find the match and the resulting recordset is always empty.

I’ve tried formatting the Date to US format before I use it in the Select statement

Code:

Format(msg.SentOn, "mm/dd/yyyy h:mm")

Then only dates up until the 12th of each month get duplicated.

Any tips or clues?

Reply | Quote

Replies

Is Message_Sent a Date or a Time or Date and Time ?

Doing date comparisons in SQL requires the Date to be in mm/dd/yyyy format .

So if it was just a Date I would be using

Code:

strSQL = "SELECT * FROM tblOutlookMessage WHERE Message_From = """ & msg.SenderName & """ AND Message_Sent = #" & format(msg.SentOn,"mm/dd/yyyy") & "#"

Reply | Quote

Thanks John, no it has to be data and time otherwise you might miss two messages from the same sender on the same day.

I edited my original post while you were replying.

As you can see I tried formatting in US format which does work if it’s only the date but not if it’s date and time.

Reply | Quote

In the format function m stands for months. You use it in one place to mean month, and the second time to mean minutes.

Try

Code:

Format(msg.SentOn, "mm/dd/yyyy h:nn")

Does the date stamp on the message include seconds as well?

Reply | Quote

Thanks John, sometimes I’m just not very bright, of course ‘n’ for minutes! I’ll go again.

Reply | Quote

Much better, but I had to format the date when writing it into the field as well. Maybe due to differences in hundredths of seconds.

Code:

         dteSent = Format(msg.SentOn, "mm/dd/yyyy h:nn:s")
         strSQL = "SELECT * FROM OutlookMessage WHERE Message_From = """ & strSender & """ AND Message_Sent = #" & dteSent & "#"
         Set rstMessage = dbs.OpenRecordset(strSQL)
         If rstMessage.EOF Then
             rstMessage.AddNew
                 rstMessage!Message_From = strSender
                 rstMessage!Message_Subject = msg.Subject
                 rstMessage!Message_Body = msg.Body
                 rstMessage!Message_Sent = Format(msg.SentOn, "dd/mm/yyyy h:nn:s")

Reply | Quote

If you convert your dates to longs you never have problems with date formats

strSQL = “SELECT * FROM tblOutlookMessage WHERE Message_From = “”” & msg.SenderName & “”” AND Message_Sent = ” & clng(msg.SentOn) & “”

Reply | Quote

That is an interesting suggestion.

In this specific case, this would Not give the right answers however. Longs are whole numbers, and the time component of a date is stored in the decimal portion. So by converting to Long, you are discarding the time. As was pointed out earlier, the time component is important as there can be several messages on the one day.

However, I imagine that converting to a Double instead would do the trick. (But I would want to test this to be sure it worked)

Reply | Quote

Tested and confirmed – Converting it to a double before feeding it into the SQl string does do the job.

Reply | Quote