Thread: Access Date Query
03-11-13, 16:57 #1Registered User
- Join Date
- Mar 2011
Unanswered: Access Date Query
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.
03-12-13, 05:58 #2Registered User
- Join Date
- Nov 2012
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 06:04.
03-12-13, 07:01 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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 understandand ensure the date is in the correct format.
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