Access SQL Query Dates YYMMDDHHMMSS

[dw85745]

Access SQL handles dates as mm/dd/yyyy hh:mm:ss

The Access DB I'm dealing with requires dates to be stored in a text format of YYMMDDHHMMSS:

I need to Query dates from the previous day and a timestamp value to today and a timestamp value.

For example: BETWEEN 7/23/2008 12:00:00 PM and 7/24/2008 11:11:59 AM

----------------------------------------------

On thought was to create another date column and when the YYYYMMDDHHMMSS is entered convert the date to the normal SQL query format.

Other than that anyone have a suggestion on the best way to accomplish this date query?

[anhn]

Make it clear:
12:00:00 PM is at Noon
12:00:00 AM is at Midnight
11:59:59 AM is at one-second-before-Noon

Not very sure but if what you mean is the datetime field is a Text field that has fix length of 12 in format "YYMMDDHHMMSS" then it can be:

BETWEEN Format$(Date - 1 + TimeSerial(12,0,0),"yymmddhhmmss") AND Format$(Date + TimeSerial(11,59,59),"yymmddhhmmss")

or

BETWEEN (Format$(Date - 1,"yymmdd") & "120000") AND (Format$(Date,"yymmdd") & "115959")

Noted that we are comparing a String with another two Strings here.
That was a bad choice when storing date-time values with Text field because comparing strings is much slower than comparing real date/time values (equivalent with Double).

[dw85745]

Thanks for the response Anhn.

It is noon of the previous date to noon of the date.

I don't believe this will work as it is using the current date.

What I need to do is Query using the DB field format of YYYYMMDDHHMMSS
and group the records by date taking into account the day as represented by the times previous noon to noon.

[anhn]

Don't you mean "today" is current date?

Your issue was not clear. Post your full query I will help to fix it.

[anhn]

You can adjust the current Text DTField of format "YYYYMMDDHHMMSS" to a Date only value, eg.:
080731102335 --> 7/31/2008 10:23:35 AM --> 7/31/2008 or "080731"
080731131015 --> 7/31/2008 01:10:15 PM --> 8/01/2008 or "080801"

* AdjDate is a real Date value:

SELECT DateSerial(Left$(DTField,2), Mid$(DTField,3,2), Mid$(DTField,5,2)) - (CLng(Mid$(DTField,7,6)) >= 120000) AS AdjDate, ... ...

* AdjDateStr is a date text value in format "YYMMDD":

SELECT IIf(CLng(Mid$(DTField,7,6)) < 120000, Left$(DTField,6), Format(DateSerial(Left$(DTField,2), Mid$(DTField,3,2), Mid$(DTField,5,2) + 1), 'yymmdd') As AdjDateStr, ... ...

Now, you can group records by new calculated field.

[dw85745]

Thanks Anhn:

Will give that a shot tomorrow.

Never seen a conditional IIF used in an SQL statement.

Have a nice day.

David.

[dw85745]

Hi Anhn:

I'd thought I'd start by just trying to query one day of data.
Not worry about BETWEEN

As discussed the DB field is in the format:YYYYMMDDHHMMSS with time in Military Format

So in order to group (Because of SQL and BETWEEN) it is my understanding I need to convert to a MS standard date format.
20080731102335 --> 7/31/2008 10:23:35 AM

I entered this and the Query is accepted so ASSUME syntax is correct.

PARAMETERS [pAcctNo] Text, [pBuySell] Text;
SELECT IIf(CLng(Mid$(tblFills.fldFillDate,9,6)) < 120000, Left$(tblFills.fldFillDate,8), Format(DateSerial(Left$(tblFills.fldFillDate,4), Mid$(tblFills.fldFillDate,5,2), Mid$(tblFills.fldFillDate,7,2) + 1), 'yyyymmdd') ) AS AdjDateStr, tblFills.fldFirmID, tblFills.fldAcctNo, tblFills.fldFillOrderNo, tblFillDetail.fldFillSplitNo, tblFillDetail.fldFillBuySell, tblFillDetail.fldFillQty, tblFillDetail.fldFillPrice
FROM tblFills INNER JOIN tblFillDetail ON tblFills.fldFirmID =  tblFillDetail.fldFirmID
Where tblFills.fldFillDate = AdjDateStr And tblFills.fldAcctNo = [pAcctNo] And tblFillDetail.fldFillBuySell = [pBuySell]
ORDER BY AdjDateStr;

I then get prompted for:

pAcctNo -- Enter 12345
pBuySell -- Enter String of B
AdjDateStr -- Where I've tried 20080515; 5/15/2008; 05/15/2008

No records are grouped and returned for that day.
Yes there are records and another Query I have returns them correctly.

======================
Where did you come up with < 120000. I know it's checking against the time but How did you derive the figure ??

[anhn]

Try this:

PARAMETERS [pAcctNo] Text, [pBuySell] Text, [FillDate] Text;
SELECT F.fldFirmID, F.fldAcctNo, F.fldFillOrderNo, F.fldFillDate,
D.fldFillSplitNo , D.fldFillBuySell, D.fldFillQty, D.fldFillPrice
FROM tblFills AS F INNER JOIN tblFillDetail AS D ON F.fldFirmID = D.fldFirmID
WHERE F.fldAcctNo = [pAcctNo] And D.fldFillBuySell = [pBuySell]
AND Format(CDate(Left(F.fldFillDate, 4) & "-" & Mid(F.fldFillDate, 5, 2) & "-"
& Mid(F.fldFillDate, 7, 2) & " " & Mid(F.fldFillDate, 9, 2) & ":"
& Mid(F.fldFillDate, 11, 2) & ":" & Mid(F.fldFillDate, 13, 2)) + 0.5, "yyyymmdd") = [FillDate]
ORDER BY F.fldFillDate;

Parametter [FillDate] must be input as a String in format "yyyymmdd" such as 20080515
Firstly fldFillDate will be reformated from yyyymmddhhmmss to yyyy-mm-dd hh:mm:ss to form a valid date format recognised by Access.
The CDate() function converts fldFillDate to an Access Date/Time value.
+ 0.5 : adds 12 hours to this Data/Time value so
if TimeValue < 12:00:00 PM then DateValue unchanged,
if TimeValue >= 12:00:00 PM then DateValue will be added by 1.
The Format() function will remove TimeValue and converts DateValue back to format "yyyymmdd".
You can use this value to compare with parameter [FillDate].

[dw85745]

Anhn:

Thanks for working with me on this.

The query does return the correct records from
noon the day before the date entered
to noon of the date entered.

NOT what I would have Expected!!!

In fact I'm not sure why 0.5 would cause
the Query to back up a day rather than
starting at midnight of the day entered
and then increase that by 1/2 a day to
get to noon of the date entered --
like DateAdd.

Further since there is no BETWEEN clause
I would have expected an Empty Query Recordset since
no record on the date entered has a time
at exactly noon.

================================================

The more I look at this the more confused I get!!!!!!

================================================

I think (unless you disagree -- feedback appreciated)
that changing the DB date string field to a date field
and adjusting all dates to Univeral Time would
greatly simplify any Query and would allow easy use
of the BETWEEN clause.

This would give a common base date which could be adjusted
to any timezone and eliminate the need for a string
date which includes the timezone identifier.

===============================================

One related issued is how Europe (and I assume Australia)
handles Access dates?

To the best of my knowledge, Access stores dates as
a double with the integer portion being the date and
the decimal portion being the time.

When the date is displayed (in an Access date field)
it is presented in a mm/dd/yyyy hh:mm:ss AM/PM

When the Date/Time is extracted it can be displayed
using Format in European time.

So are you using a US date format for any SQL queries
and they displaying as desired or is their a Australian
service pack which allows queries using the European
date.

[anhn]

Reread all your posts those make me confuse: I don't know what you want to achieve.

Instead of using BETWEEN, a WHERE condition does advance the Date/Time to half a day (or 12 hours) so you need to compare the Date value only. That is the same way as when you use BETWEEN but it is much simpler conversion.

You have a quite good knowledge on Date data type in Access, I don't know why at the beginning you stored fldFillDate as a Text field, that was very bad in practice.

Do it as what you said.

[dw85745]

Thanks for input and all the help.

FYI -- The date Text field already existed. Just trying to deal with it!!