My Access query returns bad results with reversed date format

Question

I have an application written in classic ASP hosted in local.
This application is using a Microsoft Access Database.
My problem is with this request, I want all the data of the day:

`SELECT * FROM TABLE WHERE Date= #01/03/2020#`

I get the results like this:

do while not rs.EOF 
   response.write(" " & rs("Date"))
   rs.movenext
loop

I'm only extracting the date right now and it's supposed to be the same as the date in the request.
However my results are 03/01/2020, 03/01/2020.

I do have a function to format my ouput:

function displayDate(dateToClean)
    dateToShow = Right("0" & Day(dateToClean), 2) _
        & "/" & Right("0" & Month(dateToClean), 2) _
        & "/" & Year(dateToClean)
    'If the date length is too small, it's null (0/0/0)
    if (len(dateToShow) < 10) then
        displayDate = ""
    else
        displayDate = dateToShow
    end if
end function

I tried to change the date format in my database thanks to this doc:
https://support.office.com/en-us/article/format-a-date-and-time-field-47fbbdc1-52fa-416a-b8d5-ba24d881b698
But It didn't resolve my problem. Hope I was understandable enough.

For a start, Date is a reserved word and should not use reserved words as names for anything. If you do, then use [ ] to delimit in query: `[Date]`. See if this article helps http://allenbrowne.com/ser-36.html — June7, Apr 08 '20 at 16:07
This is not the original name, I changed it by "Date" for this post. — perap rnlad, Apr 08 '20 at 18:24
@Lankymart, Thanks but no. I do have a kind format problem but everything is fine in my code. I'd rather think the problem is in my database, but I can't find it. — perap rnlad, Apr 08 '20 at 18:40

score 1 · Accepted Answer · answered Apr 09 '20 at 01:47

Just keep in mind that the internal date format is a internal number. You can format the output anyway you want (as your function does). However, WHEN you are doing a query, you have to follow ISO format, or mm/dd/yyyy (USA format). This requirement does NOT change regardless of your regional settings, and you can't change this ISO/USA format requirement for a query (pull of data).

So query/pull of the data - date format must be ISO, or USA (mm/dd/yyyy). Once you pull such data, then you can use your function to display the date however you want.

So, to pull, you need:

SELECT * FROM tblInvoices where InvoiceDate = #01/03/2020#

or

SELECT * FROM tblInvoices WHERE InvoiceDate = #2020-01-03#

For both above, we are talking Jan 01, 2020

dim dtInvoiceDate as date

dtInvoice = #01/03/2020#    - MUST use USA format.

dim strSQL as string

strSQL = "SELECT * from tblInvoice WHERE InvoiceDate = " & "#" & Month(dtInvoice) & "/" & _
    Day(dtInvoice) & "/" & Year(dtInvoice) & "#"

So, just keep in mind that any query pull is regardless of what format or even what your computer regional settings are. You can't change the database engine settings for this - it is ALWAYS USA format, or ISO format.

Once you pull the data, or use a response. Write, then such a string output WILL display in your computers regional settings. As noted if you want to force a format regardless of your computer settings, then that handy dandy function you have is a rather nice approach. Just keep in mind that your function is ONLY of use for display. For pulling data, you can't use that format.

So, try to use + manipulate any date value as a actual date type, and ONLY format at output time, and for any query pull of data, you have to format for USA or ISO.

Thanks you really helped me there :) – perap rnlad Apr 09 '20 at 11:13 — perap rnlad, Apr 09 '20 at 11:13

Gustav · Answer 2 · 2020-04-08T20:04:13.420

-1

To play safe, use the ISO sequence. For March 1th:

SELECT * FROM TABLE WHERE [Date] = #2020/03/01#

Having that in place, format your output:

response.write(" " & FormatDateTime(rs("Date"), 2))

If that format doesn't fit your purpose, your method is not safe. Use the method linked to by Lankymart in his/her comment.

edited Apr 08 '20 at 20:04

answered Apr 08 '20 at 18:19

Gustav

53,498
7
29
55

Sorry, the desired format is dd-mm-yyyy. This is the format setted up in my code and in my database. I would like to get all the data of the first March but it's returning me the third january. I do have a function to format my output but even with it, it changes nothing.. – perap rnlad Apr 08 '20 at 18:29
2

The `Format()` command isn’t available in VBScript you’re probably thinking of `FormatDateTime()` but that is very limited. [This answer](https://stackoverflow.com/a/22575530/692942) details how to format dates in Classic ASP / VBScript. – user692942 Apr 08 '20 at 18:32
I added my function to format my output in the post. – perap rnlad Apr 08 '20 at 18:47
You are confused. A stored date value carries _no format_. A format is only used for display or to build string expressions to concatenate in SQL. See edited answer, please. – Gustav Apr 08 '20 at 20:06
@Gustav No I got that. I'm extracting the day, month and year of a date object to make a string that I can use in my SQL request or to print it. That's the purpose of my function, convert a date into a string. – perap rnlad Apr 08 '20 at 21:53
That's your mistake, as that format is doomed to fail in SQL. For display, it is OK; any formatted string can be used for display. – Gustav Apr 09 '20 at 06:43

My Access query returns bad results with reversed date format

2 Answers2