将日期四舍五入到SQL查询中的日期 [英] 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)
我不确定截断表"tblSearchEngine02"上日期的最佳方法.
Im not sure the best way to truncate the date on table "tblSearchEngine02"..
推荐答案
一种方法是将日期/时间格式化为日期字符串.如果使用YYYY/MM/DD
,它将正确排序.否则,您可以将日期/时间转换为整数以修剪时间,然后再转换回日期/时间类型.
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")
这里有一个转换为日期的示例(最终结果将是日期/时间数据类型,因此根据您的语言环境信息,它可能会显示为03/16/2014 00:00
)
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]))
这篇关于将日期四舍五入到SQL查询中的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!