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

    Question Unanswered: SQL: using Datepart with Access/VBA sql query

    I have a date field of type General, hence storing date, minutes, seconds, milliseconds.

    In one situation, when retrieving records for a specific date, I need to specify that the date retrieved is equal to or newer than a certain record.

    However, I also want to retrieve records that is registered EARLIER THAT SAME DAY as the reference record, hence the >= should only check the date , not the time. I hope this is possible by using datepart, but need some help with the syntax:

    General syntax may be as follows:
    SELECT DATEPART('yyyy','2004-12-20 12:00:00') AS Year
    etc.

    How do I ask for only dd-mm-yyyy to be returned from a general date field?

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

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    access datetimes are days-point-fractionaldays

    you could cheat and play with the date as a number: simply drop the stuff after the decimal

    or you could assemble a date in the "official" way:

    midniteDate = dateserial(year(anyDate), month(anyDate), day(anyDate))

    either way, your datetime looks like 9999.0 where the .0 is zero hours, zero minutes, zero seconds = 00:00:00 = midnight at the start of the day

    izy
    currently using SS 2008R2

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

    Question

    Quote Originally Posted by izyrider
    access datetimes are days-point-fractionaldays

    you could cheat and play with the date as a number: simply drop the stuff after the decimal

    or you could assemble a date in the "official" way:

    midniteDate = dateserial(year(anyDate), month(anyDate), day(anyDate))

    either way, your datetime looks like 9999.0 where the .0 is zero hours, zero minutes, zero seconds = 00:00:00 = midnight at the start of the day

    izy
    Yes, ok, will check the 9999.0 possibilities.
    Earlier today I found that I could achieve one goal by setting the form field to general date (as the underlying field format), but then use =date() as the default value, so that if the user accepts the default, the time will be 00:00:00, if he/she changes the date it is only displayed as dd.mm.yyyy and still storing 00:00:00 in the time part, OR the user can add a space, and then nn:nn to specify a time. The field will accept "all" exept from ommitting the minutes part.

    When a query is run filtering on >= #dd.mm.yyyy#, I get the output I want, ie. ALL on that date, regardless of the time stamp.

    BUT: I want the query to use the value of the current record in the form as the parametre. Here is where I am unsure:

    The date in that field would either be displayed as a short date (having 00:00:00 as the time part), OR in full, with a certain time of that particular day specified.
    I need to retreive all records from that date, also those earlier than the time stamp of the current record, and preferably with the option of using a query (for other reasons/circumstances), not only through VBA tricks.
    That is why I want to know if I can use Datepart for this, as part of the SQL syntax.

    Could you give me an example within a SELECT statement where its criteria gets the content from the form field (passing parametre) but only uses the dd.mm.yyyy part of the data?

    If the data passed on is "dd.mm.yyyy 14:56:04", put into the >= criteria, I want the query to execute only >= #dd.mm.yyy#.
    I know I can trim it in VBA first, but my question is if I can use the DATEPART for this. Sometimes I want the query to look up data like this from a table without VBA intervention, then I would need to strip it somehow.

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

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    strSQL = "SELECT * FROM myTable WHERE myDate >= dateserial(year(anyDate), month(anyDate), day(anyDate));"

    ??

    i admit that i am abusing datetimes, treating them as numbers. but it works!

    izy
    currently using SS 2008R2

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

    Thumbs up

    Quote Originally Posted by izyrider
    strSQL = "SELECT * FROM myTable WHERE myDate >= dateserial(year(anyDate), month(anyDate), day(anyDate));"
    Thanks! That looks like a solution. :-)

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

  6. #6
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Ah, yes, and yet another situation where there could be a separate sorting criterias entered manually for certain records/situations, which when they occur will have presedence over the time part of the date field, but when not set, will appear equal and hence the time part of the date should be used.
    I need to sort it both visually, and to retrieve into a recordset:

    1. sort using the serial method to get dd.mm.yyyy

    2. within that date, on a separate non-date field, sort on this field

    3. within the records that still are the same, on the time stamp part of the first date field (the one used in 1.):
    - use the full date field without serial method as 3. criteria? Alternatively use that method to only get the time stamp for the 3. sorting?

    Is this unnecessarily complex?

    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
  •