Comparing dates - Access and SQL

[tuxalot]

I have an Access table with a DATETIME field. The field is not formatted as general, I left it as default (blank)

I have created a SQL table on the web using ODBC by exporting my table. That field is also DATETIME.

When viewing the dates, I see that:

Access is storing data like this:
11/26/2012 4:27:39 PM

I am learning that this is just how it appears, and that Access actually stores the data as a floating point number.

Looking at my SQL table, I see the data presented as:
2012-11-27 23:06:59

I am trying to make data comparisons. I wrote SQL like this and it does not work:

SELECT tblAndroid.Rspns, 
   tblAndroid.RspnsComment, 
   tblAndroid.AnswerTIme, 
   tblAndroid.ImgPath
FROM tblResponses, tblAndroid
WHERE (((tblAndroid.RspnsID) 
   Like [forms]![frmUpAndroid]![txtRspnsID]) 
      AND ([tblAndroid].[AnswerTIme]
         >[tblResponses].[AnswerTimeLOC]));

tblAndroid is my SQL table and tblResponses is my local table. I was trying to escape the date using "#" but I don't quite understand where to place those. I also read that converting the date/time to ISO format would make the date/time an integer and make comparisons possible, but I could not find examples how to do that.

Any help is appreciated.

[TheSmileyCoder]

Both systems store the date as a double, and it doesn't matter how they are formatted and presented.

Now I don't have much SQL server experience, but I think there might be something about SQL server and access using different zero points for reference. You might want to look into that a bit.

[NeoPa]

Smiley's point seems interesting. Let us know if you find that to be the case. I must say I'd be surprised if it were so (Smiley knows his stuff - It's just that I'd expect more problems if that were true).

As for the hashes, it's good thinking, but hashes are only used for date literals (See Literal DateTimes and Their Delimiters (#)). You aren't using literals, so needn't worry about them.

Do you have any example data that is behaving in a way you wouldn't expect? My expectation would be that this SQL should work as is. My only guess as to the problems you're experiencing is that maybe the data values are not exactly as you expect them to be. Maybe Nulls or maybe date- or time-only values on some records.

[TheSmileyCoder]

I am in a bit of a rush, so I will admit to not reading the article at length, but I think you may find it helpfull regardless:
SQL-Server-and-Access-Zero-Date-Difference

Please let us know how it works out.

Best of luck with your project.

[NeoPa]

Your point is irrefutable Smiley. Clearly, though, there is something in the interface that minimises the problems associated with this, even if it doesn't make it go away entirely. In the circumstances, I can't be too surprised if that does actually lead to an understanding and explanation of Tux's problem. Still not sure, but conceivably it could, certainly.

[TheSmileyCoder]

Now that zmbd was kind enough to format your SQL in a more readable way, I also can't help but notice that you have no join between your two tables. Is this by intent?

[tuxalot]

Thanks all for your assistance. Smiley, I have tried running the code joining on RspnsID as that field is shared by both tables and still no good. Let me provide some background. Maybe there is an easier approach to what I am looking to accomplish.

Background: I am having an Android app developed for my survey database built in Access. Once a survey is begun, data is uploaded to a SQL table using ODBC. Android users can download data from the SQL table for processing on the mobile device. I understand there is a built-in database in Android so I am utilizing this to prevent having to maintain an Internet connection.

Back in Access, I want to give users the ability to work on the same survey that I previously uploaded into SQL (some prefer to use their laptops). To track changes, I have a DATETIME field that adds data using Now() if data is updated for a given record.

An Android user has the same functionality to track their changes. They too are adding a timestamp to records as they update them. At any time an Android user can choose to update the SQL table manually by clicking a button.

Now the problem. I want to compare datetime between records in the SQL table and Access. Only records that are more recent in SQL should be updated in Access.

Sorry for the long winded explanation but now that the problem is better framed maybe there is another way to go about it.

[zmbd]

To add to post #4 http://blogs.msdn.com/b/ericlippert/.../16/53013.aspx

http://www.joelonsoftware.com/items/2006/06/16.html

Very interesting reads.

[Rabbit]

Export out as a formatted string and import in as date time.

[tuxalot]

Thanks for the nudge Smiley. I added appropriate joins and it is working. Here is the code:

SELECT DISTINCT tblAndroid.RspnsID,
  tblAndroid.QstnID,
  tblAndroid.Rspns,
  tblAndroid.RspnsComment,
  tblAndroid.AnswerTIme,
  tblAndroid.ImgPath
FROM tblAndroid
INNER JOIN tblResponses
  ON (tblResponses.QstnID = tblAndroid.QstnID)
  AND (tblAndroid.RspnsID = tblResponses.RspnsID)
WHERE (((tblAndroid.RspnsID)
  Like [forms]![frmUpAndroid]![txtRspnsID])
   AND ((tblAndroid.AnswerTIme)
    >[tblResponses].[AnswerTimeLoc]));

[TheSmileyCoder]

You are most welcome. Best of luck with your project, it sounds quite interesting.

[tuxalot]

Thanks Smiley. Now I've changed the select query to an update query and it's working, although it seems bass-ak-wards what I had to do to make it work. Here is the SQL:

UPDATE tblResponses
INNER JOIN tblAndroid
  ON (tblResponses.RspnsID = tblAndroid.RspnsID)
  AND (tblResponses.QstnID = tblAndroid.QstnID)
SET
  tblResponses.Rspns = [tblAndroid].[Rspns],
  tblResponses.RspnsComment = [tblAndroid].[RspnsComment],
  tblResponses.ImgPath = [tblAndroid].[ImgPath],
  tblResponses.AnswerTimeLoc = [tblAndroid].[AnswerTIme]
WHERE (((tblResponses.RspnsID)
  Like [forms]![frmUpAndroid]![txtRspnsID])
   AND ((tblAndroid.AnswerTIme)>[tblResponses].[AnswerTimeLoc]));

Mind you that I am evaluating dates and updating records from SQL to Access if dates are more recent in SQL. So I intentionally made the date of a few rows in tblAndroid (my SQL table) more recent than the corresponding rows in tblResponses (my Access table) to test if those rows would update in tblResponses.

Again, the SQL above makes sense but in the query designer I had to place these values:
Field: Rspns
Table: tblResponses
Update To: [tblAndroid].[Rspns]

etc...

I am not updating to tblAndroid, rather taking the updates from tblAndroid. Am I looking at this wrong?

[TheSmileyCoder]

I guess we are down to semantics here. Its the field tblResponses.Rs pns that will be updated TO the value of what you specify, which can be a constant, a function call, or in this case a value in a related record.

[tuxalot]

The light bulb is on. I see. The key part of that understanding in my pea sized brain is that we are updating to a VALUE, and this process does not indicate directionality of the update to a TABLE.

Got it.

Thanks for the education.

[TheSmileyCoder]

No problem. Always happy to help.