Rounding dates to the day in an SQL query

Question

I am stuck with something. I am trying to take a long column of dates of which are formated to show also hours and minutes and run a Group query to paste values at the date level without acknowledging the differences in hours and minutes.. Unfortunately I have no clue how to start. The code i put together so far which returns each grouped date with the hour and minutes is as follows:

st_sql = "INSERT INTO [tblSearchEngine03] ([Date])" & _
    "SELECT [tblSearchEngine02].[Date]" & _
    "FROM [tblSearchEngine02]" & _
    "GROUP BY [tblSearchEngine02].[Date]" & _
    "ORDER BY [tblSearchEngine02].[Date]"

    Application.DoCmd.RunSQL (st_sql)

Im not sure the best way to truncate the date on table "tblSearchEngine02"..

Answer 1

One way of doing this is to format the date/time as a date string. If you use YYYY/MM/DD it will sort properly. Otherwise you can convert the date/time to an int to trim off the time and then convert back to a date/time type.

Here is an example of formatting as string:

Format([tblSearchEngine02].[Date], "yyyy/mm/dd")

Here is an exmple of converting to get to a date (the end result will be a date/time data type so it might render as 03/16/2014 00:00 depending on your locale info)

CDate(CInt([tblSearchEngine02].[Date]))

Answer 2

Access stores its dates as floating point numbers where the integer part is the number of days since Jan 1, 1900 and the fractional part is the fraction of the day (time of day). Access is quite happy to treat these dates as numbers without doing any conversions, so:

fix([tblSearchEngine02].[Date])

will trim the fractional part of the day and set the time back to midnight and allow you to group by day.

Answer 3

Focus on the SELECT piece first. You can use DateValue() for your Date field values. Start with this as a new query in the Access query designer:

SELECT DateValue(se02.Date)
FROM tblSearchEngine02 AS se02
GROUP BY se02.Date
ORDER BY se02.Date

Or you could use DISTINCT instead of GROUP BY:

SELECT DISTINCT DateValue(se02.Date)
FROM tblSearchEngine02 AS se02
ORDER BY se02.Date

After you have the SELECT working correctly, you can convert it to an INSERT query (the Access query designer calls it an "append" query).

And when you later build the same statement in your VBA code, include Debug.Print st_sql so that you can view the completed statement text in the Immediate window and make sure it is what you expected. (You can use Ctrl+g to go to the Immediate window.)