Unanswered: Get Just Date (Distinct) From Date/Time Field
How do I write a query to get just the distinct DATE only (strip out the time, if it is there) from a date/time field (Access 97)?
Some records have a time value with the date in the field (e.g. "3/17/2004 12:31:02"), other records have just the date in the field (e.g. "3/17/2004") in which case I believe the time defaults to 00:00:00.
Currently, when I'm selecting distinct date via:
SELECT DISTINCT myDateTimeField
I get EVERY date, since each date has a unique time with it. My goal is to display only one date for each date listed in the html form picklist, no matter how many records have that same date but differing times. All I have been able to figure out so far from anyone is that I think I need to first strip out the time, then select distinct for the date.
I'm accessing the table in Access 97 via ODBC to populate a picklist in ColdFusion/HTML, so I am not using forms/listboxes, etc. in Access, so Access forms will not help me.
Someone else thought that the time all needs to be converted to 00:00:00 (if there is time in the field along with the date), but I have no idea how to write it.
Originally posted by Jerrie
SELECT DISTINCT Format([myDateTimeField], "Medium Date");
I tried this, but got an ODBC 07001 error (Wrong Number of Parameters). I changed the double quotes to single quotes, but then it did not find the date/time field in the query (i.e. when I tried to display the query results, it said the column (my date/time field) was not defined in the query.
SELECT DISTINCT Format([TableName].Review_Date,"Medium Date") AS Expr1
FROM [IDS LIST];
I just wanted to make sure my situation was clear---that I'm using ColdFusion to run the query via ODBC against an Access 97 table, so the query is not set up in, or being run in, Access itself--rather, it's coming from outside Access against an Access table which is just used to store data.
My particular query is running against two tables, and in reality looks like:
SELECT DISTINCT DateInitiated (the part I'm having trouble with)
FROM tblIssues, tblIssueStatus
WHERE tblIssues.Release = 1
AND tblIssues.IssueStatus = tblIssueStatus.status
AND tblIssueStatus.status = 1
The "DateInitiated" field is in tblIssues. So, I tried:
SELECT DISTINCT Format([tblIssues].DateInitiated,"Medium Date") AS Expr1
FROM [IDS LIST] (not sure what this is--Is "IDS LIST" the name of my table tblIssues? and how can I also include tblIssueStatus?)
I've also found that it doesn't like the semicolons.
An easier way to do this while preserving the data type as date time is to use:
Date times are stored as floating point values, the integral part is the day represented as an offset from a fixed date and the decimal part is the time of day, so, by using Int() you are rounding towards negative infinity which effectively removes the time. However the data type is then a number not a date, so put CDate around that to force it to a date data type.