Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: Get Just Date (Distinct) From Date/Time Field

    Hi -

    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.

    Any help would be greatly appreciated! Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    SELECT DISTINCT Format([myDateTimeField], "Medium Date");

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    Unhappy

    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    in SQL it would be..

    SELECT DISTINCT Format([TableName].Review_Date,"Medium Date") AS Expr1
    FROM [IDS LIST];

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Originally posted by Jerrie
    in SQL it would be..

    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.

    Thank you for spending time and effort on this!

  6. #6
    Join Date
    Jul 2009
    Posts
    1

    An easier way

    An easier way to do this while preserving the data type as date time is to use:

    CDate(Int([TableName].Review_Date))


    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.

    Hope this helps.

  7. #7
    Join Date
    Mar 2010
    Posts
    2
    Thanks TheToid

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •