Marchese Il Chihuahua Marchese Il Chihuahua - 1 month ago 4
SQL Question

Rounding dates to the day in an SQL query

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

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]))