Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: access-sql datepart question

    can anyone tell me how to get only the day, month and year from a general date field inside an access-sql query without using parametres/vba etc.?

    Here is what I would like to do with a normal Access2002 query:
    first fetch records in groups, buy using a combination of 3 fields in the same table(only one source table/query behind this query):
    When making the unique 3-field key, there is a date field involved, that is of type General, hence storing "dd.mm.yyyy hh:mm:ss".

    Now, I need to create the unique 3-part key using only dd.mm.yyyy from the general date field, because after grouping, within each group, I want to sort by a different field. In order for the group to identify the correct records for each group, I have to disregard the time stamp in that same field.

    Can I construct such a query from something like this?:
    WHERE myDate < dateserial(year(anyDate), month(anyDate), day(anyDate))

    For my knowledge and other needs, I'd like to know how to to fetch the anyX part directly from the system clock, from a different field in the same record, and even from a subquery looking into a different table. (all using access-sql, not vba or parametres).

    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This is messy as hell... can I ask why you're using partial dates as primary keys?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Arrow

    Yes, sorry about the messy explanation, perhaps this is clearer:

    I need column(date) passed and used as a date with no timestamp.

    Output sample:
    CID,ric,dateT, srt, TID (primary key)
    32, 4, d1.m.y h2:m2:s2, 1, 829
    32, 4, d1.m.y h1:m1:s1, 2, 828
    32, 4, d1.m.y h3:m3:s3, 2, 827

    (client id, stock id, date(full), the second sort field(srt) and the transaction id which is the unique key of the underlying table. The records could have been entered in the wrong order, so I do not sort on the primary key in this situation.)

    Condition that is void if a timestamp is present:
    - for each CID/RIC combination I want records grouped by date X, date Y, etc., then for each group, I would order by field SRT first, THEN by the whole dateT field including the timestamp...

    (whenever two SRT's are equal, it would sort on the full dateT field)

    So summon it all up:
    The grouping uses only dd.mm.yyyy in order to identify each group uniquely based on CID/RIC/date(dd.mm.yyyy extracted from dateT), but then, when the records are grouped, I need to order by SRT, and then by the full dateT field including the time part.

    D.
    Last edited by kedaniel; 07-15-04 at 12:16.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Arrow

    I updated the sample in my last posting, added one row and adjusted the time stamp to illustrate the sort order of dateT and srt.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    have you considered cheating by grouping on
    Fix(anyDate)

    Fix() removes the stuff after the decimal ( = sets the time to 00:00:00)

    i didn't try it: let me know if it works.


    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Lightbulb

    final result that works:

    Code:
    SELECT qry_u1.cid, qry_u1.ric, qry_u1.sort, qry_u1.datet, qry_u1.tid
    FROM qry_u1
    WHERE (((Exists (select null from qry_u1
    where cid = qry_u1.cid AND ric = qry_u1.ric AND datet = DateSerial(Year([qry_u1].[datet]),Month([qry_u1].[datet]),Day([qry_u1].[datet])) 
    GROUP BY cid, ric, datet))<>False))
    ORDER BY qry_u1.cid, qry_u1.ric, qry_u1.sort, qry_u1.datet, qry_u1.tid
    WITH OWNERACCESS OPTION;
    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  7. #7
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Lightbulb

    Quote Originally Posted by izyrider
    have you considered cheating by grouping on
    Fix(anyDate)
    Fix() removes the stuff after the decimal ( = sets the time to 00:00:00)
    i didn't try it: let me know if it works.
    izy
    With izy's Fix() tip, which is also working:
    Code:
    SELECT qry_u1.cid, qry_u1.ric, qry_u1.sort, qry_u1.datet, qry_u1.tid
    FROM qry_u1
    WHERE (((Exists (select null from qry_u1
    where cid = qry_u1.cid AND ric = qry_u1.ric AND datet = Fix([datet]) 
    GROUP BY cid, ric, datet))<>False))
    ORDER BY qry_u1.cid, qry_u1.ric, qry_u1.sort, qry_u1.datet, qry_u1.tid
    WITH OWNERACCESS OPTION;
    BUT: while the first DateSerial returns a proper date: dd.mm.yyyy, using Fix() returns the date serial number...:
    I checked by switching the date output with Fix:
    Code:
    SELECT qry_u1.cid, qry_u1.ric, qry_u1.sort, Fix([datet]) as FixedDate, qry_u1.tid
    FROM qry_u1
    WHERE (((Exists (select null from qry_u1
    where cid = qry_u1.cid AND ric = qry_u1.ric AND datet = qry_u1.datet 
    GROUP BY cid, ric, datet))<>False))
    ORDER BY qry_u1.cid, qry_u1.ric, qry_u1.sort, qry_u1.datet, qry_u1.tid
    WITH OWNERACCESS OPTION;
    Which gave the following output:
    CID, ric, sort, FixedDate, tid
    32, 4, 1, 37971, 802

    instead of:
    CID, ric, sort, DateSerialOutput(dateT), tid
    32, 4, 1, 16.12.2003, 802

    So, DateSerial gave the date as I would expect displaywise, Fix() gave the serial date number...

    For my need with this particular query it would not really matter, but it might be important to keep in mind for certain situations.

    Out of curiosity - is there a sql-way of converting the output of Fix to show the actual date instead of its serial number?

    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

Posting Permissions

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