Query where I only want Date from a date/time field.

[Vergy39]

I am having a problem with datepart or format functions. I have a table that only has one column that houses date/time data. I want to pull data using this column, but I only want the date portion. I dont want the time. For example, I am using this query:

SELECT dbo_CallData.RcdCreateDateTime, Count(*) AS [#OF]
FROM dbo_Categories INNER JOIN dbo_CallData ON dbo_Categories.CatID = dbo_CallData.CatFK
GROUP BY dbo_CallData.RcdCreateDateTime, dbo_CallData.CatFK, dbo_Categories.CatName, dbo_CallData.GroupFK
HAVING (((dbo_CallData.RcdCreateDateTime) Between #8/26/2012# And #9/15/2012#) AND ((dbo_CallData.CatFK)=33) AND ((dbo_CallData.GroupFK)=12));

I have tried DatePart and Format but get errors or no results at all. The query above returns several lines because it includes the data/time. Here is an example:

Query1RcdCreateDateTime#OF8/26/2012 8:59:55 AM18/27/2012 6:21:10 AM18/27/2012 8:26:13 AM18/27/2012 8:37:48 AM18/27/2012 10:24:52 AM18/27/2012 10:29:59 AM18/27/2012 11:45:40 AM18/27/2012 12:48:22 PM1

I want it to say 8/26/2012 56
8/27/2012 95
8/28/2012 158
etc.

Any assistance is greatly appreciated.

Thanks
David V

 

[boblarson]

For getting just the dates when your field is date/time, you can use

Between #8/26/2012 12:00:00 AM# And #9/15/2012 11:59:59 PM#

 

[Vergy39]

I guess that was not a good example of what I am getting. Here is a better example:

RcdCreateDateTime #Of
8/26/2012 12:15:48 1
8/26/2012 12:18:28 1
8/26/2012 1:15:16 1
etc

Thanks

 

[boblarson]

But for the return field you can use:

Format([DateField], "mm/dd/yyyy")

 

[boblarson]

To pull only the date, you could also use

DateValue([DateFieldNameHere])

and then in the criteria put what you have currently.

 

[Vergy39]

Thanks Mr. Larson, I have tried that as well, but still get the result above. I need to remove the time portion from the date/time field in the query.

thanks

 

[Vergy39]

To pull only the date, you could also use

DateValue([DateFieldNameHere])

and then in the criteria put what you have currently.

This worked, thank you so much.

David V.