How to query for quizzes submitted during a specific date range?

[msanders]

I am using MS Access to join my tables and look at the resultant data.  When I bring in the quiz_submission_fact date field as type "date/time", MS Access errors out.  It does not recognize it as a date/timestamp (2015-11-05 15:10:18.738301). Any suggestions on how import the data as a true date/timestamp would be appreciated. 

[ccoan · Accepted solution]

Hello Mary,

It's true the 'date' field inside of the quiz_submission_fact is not a Date/Time field. In fact it is a timestamp field. Which in reality is a bit of a misnomer, as it's purpose isn't really meant for "time". In fact it's meant to provide a unique identifier for when something was accessed. However just because in reality it doesn't have to do with time we can work around this in order to get the values in DateTime. In order to perform the filtering you would like too. The Information you would do to update one specific value would look like:

DateValue(Replace("06-10-2010 02:42:00", "-", "/"))

This will keep just the date (i.e. drop the hours/minutes/seconds). However this could be information that you would want. In which case we'll want to use the CDate Function:

CDate(Replace("06-10-2010 02:42:00", "-", "/"))

With this you'll receive the hour/minutes, and the day/month/year. (HERE is a link to the CDate function documentation, and HERE is a link to the DateValue documentation for those who wish to follow up with further reading). However going through each piece of data, and converting by hand seems like kind of a pain? Luckily MS Access provides us with an Update Function.

I NOTE BEFORE DOING THIS MAKE A COPY OF WHATEVER YOU HAVE. It'd be lame to have it botched up, and have to re-download everything re-importing it as well. It should also be noted since you can't update Redshift you'd have to do this with an imported tables from flat files.

Once you've done that it'd be possible to perhaps do this through the Update function documented: HERE. An Example Query might look like:

UPDATE quiz_submission_fact
SET date=DateValue(Replace(date, "-", "/"))
WHERE date LIKE '%'