How Do I Remove "Time" from a Datetime Field

[James Parker]

Problem: I run a query based on a table that defines the Data Type for this field as a datetime. The field is called "WHEN". I do not need the time, I only need the date.

I do not know if the fix should be made only in the query, or if should and can be in the original table (Scrap_Q4).

Here is the SQL for my query:
SELECT Scrap_Q4.When, Scrap_Q4.REJ, Count(Scrap_Q4.PN) AS CountOfPN, Scrap_Q4.PN


FROM Scrap_Q4
GROUP BY Scrap_Q4.When, Scrap_Q4.REJ, Scrap_Q4.PN
HAVING (((Scrap_Q4.When) Between [Start date] And [End date]) AND ((Scrap_Q4.REJ)=[#]))
ORDER BY Scrap_Q4.When, Scrap_Q4.REJ;

Obviously, I have many things going on in the query, but my goal right now is to just remove Time from the Date.

[Robeen]

SELECT Format([DateFieldInTable],"mm-dd-yyyy") AS [YourDate]
FROM [TableName];

[Robeen]

. . . or . . . I could have just done it in YOUR query!!!

 
SELECT Format(Scrap_Q4.When,"mm-dd-yyyy"), Scrap_Q4.REJ, Count(Scrap_Q4.PN) AS CountOfPN, Scrap_Q4.PN
FROM Scrap_Q4
GROUP BY Scrap_Q4.When, Scrap_Q4.REJ, Scrap_Q4.PN
HAVING (((Scrap_Q4.When) Between [Start date] And [End date]) AND ((Scrap_Q4.REJ)=[#]))
ORDER BY Scrap_Q4.When, Scrap_Q4.REJ;

[James Parker]

Thanks Robeen. That was perfect. Made my day.

[Robeen]

You can also change this:
Format(Scrap_Q4.When,"mm-dd-yyyy")
to
Format(Scrap_Q4.When,"yyyy-mm-dd")

When it comes to sorting dates in the mm-dd-yyyy format, I've had a couple of surprises because Access will put
11-24-2011
AFTER
01-05-2012
in an ASCENDING sort - because it evaluates the mm '11' as greater than the 01 . . . and doesn't seem to check for which year . . ..

Of late, I've been putting my dates in yyyy-mm-dd format.

All the best!!