Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011

    Unanswered: Access Date Query

    Hi Guys,

    We currently have a tool that reports on our financial data. This is an access tool that we duplicate for each territory (and most importantly time zone). The access query uses SQL tables.

    The issue we face, is that the Systems Date Time format has to be correct for a further query to analyse the data. My problem is I want a query that will analyse the data, and ensure the date is in the correct format.

    Assume we have a table, with a column full of dates. All dates are exactly the same. They should represent last Fridays date. I want to build a query that will check this column, understand last Fridays date, and ensure it is set in the time zone format - e.g. US Date shows as 01/13/13, whereas UK would show 13/01/13.

    Any ideas? I have been struggling with this one for a long time and any help would be great.


  2. #2
    Join Date
    Nov 2012
    Hi Josh,

    How about this? I would first design a COUNT query that would count the number of records that have the first number of the supposed date (??/xx/yy) higher than 12 and a same count for the second number (xx/??/yy). The result should give you two numbers, one of which has to be zero - and that's the month part. Now that you know the dateformat, you can go from there... maybe select from two queries?

    That's just from the top of my head and there is a serious issue - it wouldn't work if there are no dates with days higher than 12. I suppose some checks can be done for workdays... can the dates be weekends?

    EDIT: Sorry, I didn't read it well the first time. The dates are only Fridays? So wouldn't it be possible to check if all dates are Fridays in DD/MM/YY or MM/DD/YY?

    Last edited by Myshaak; 03-12-13 at 05:04.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if your application is used wehre the date format can be dd/mm/yy or mm/dd/yyyy then I'd strongly suggest you alwasy rewfer to date literals within the application as YYYY/MM/DD the iso date format.

    I don't understand
    and ensure the date is in the correct format.
    as a format is just that, a format, providing your underlying data type is datetime then it doesn't matter what format you use to display

    you can coerce users by using datepicker controls or do your own validation

    I've always found using the alpha month a good way of reducing possible confusion and building confidence. whether you use 12 Apr 2013 or the frankly baffling Apr 12 2013 is up to you but all locales will recognise that means the 12th of April not the 4th of December.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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