I have again a date problem.

I'm Dutch so the date-layout is like dd/mm/yyy

When entering a date into my DB, the sql-statement show the correct date (dd/mm/yyyy) but when inserted into my table there occurs the following:

When my day (dd) is smaller then 13 then it switches the month and day in my record. So it is displayed as (mm/dd/yyyy), which gives problems when reading from the db.

So for instance August 6 2004 (06-08-2004) becomes in the DB 08-06-2004, June 8th 2004.

What is going wrong???

I always thought computer dates were yyyy/mm/dd, but guess not in Access!

There isn't a way to fix this, it's detecting that you entered the month 13, and then finds that isn't a real month! So it switches the two.

If you play around in the options of Acess, you might be able to find something.

Sense no one ever looks at the database directly, I'd just use some code to switch the two when retrieving it from SQL.

After reading, my problem isn't solved, for the record can both have August 6th as entry, as wel as June 8th.

When using the DateDiff function in the SQL statement as in one of my previous posts:

http://www.webxpertz.net/forums/showthread.php?t=29706

It does calculate the date wrong so the correct newsitems are NOT displayed.

How to solve this then in my SQL-statement, so I do get the correct records?

Correct, it can have August 6th and June 8th. Access with think its August 6th, when you intended June 8th.

But if you entered 13 in the month field, it wont take it, because there aren't 13 months in the regular calendar.


You can't actually format it in SQL, but what language are you using to process the data? ASP?

I'm using ASP and have a MS Acces DB.

When entering this date: 23-07-2004 (dd-mm-yyyy) it writes the correct date to the db, because 23 > 12

But when entering: 08-07-2004 (dd-mm-yyyy) it writes 07-08-2004 (dd-mm-yyyy) to the db, because 08 <= 12

The problem is when reading again from the db...I input 07-08-2004, but I read 08-07-2004 from the DB. But I want to do some calculation within the SQL-statement (see earlier link).

OK, I might have a solution, before inputting the date into the DB I already check if the day is < 13. Then I pre-switch the day and month, so when inserted, the day and month are switched back in the way I need it.

I use this function:

FUNCTION CheckDate(mydate)
IF DatePart("d", mydate) < 13 THEN
CheckDate = FormatDateTime(DatePart("m", mydate) & DatePart("d", mydate) & DatePart("yyyy", mydate), 2)
ELSE
CheckDate = FormatDateTime(mydate, 2)
END IF
END FUNCTION

So my SQL insert function becomes something like this:

my_date = "07-08-2004"

INSERT INTO mytable(to_date)
VALUES (CheckDate(my_date))
I haven't tested it yet, but I'll post my findings...

Just to make sure, did you modify the input mask of the access database for your date column, changing it to dd-mm-yyyy

In the input mask I put:

99/99/0000;0;_

So it's correct..can I work with dd/mm/yyyy to? Or will this give an error?

That is a good question. I am 98% positive on the DB I built a few weeks ago I used dd/mm/yyyy instead of 99/99/0000;0;_. If you click inside the input mask field and press F1, it should give you more details. Unfortunately, I do not have Access at my finger tips right now or else I would look it up for you.

Access will error immediately if dd/mm/yyyy does not work.

No it doesn't..it changes dd/mm/yyyy to "dd"-"mm"-"yyyy" to input it as a string.


I still have to try my previous function.

I loaded up Access and tried it.

I first put a date in the format of mm/dd/yyyy in one field. Then I changed the format of the date to dd/mm/yyyy and it switched everything for me.

I think the problem would be is that ASP thinks the date format is something else.

What datatype did you use? Date/Time or just Text?

I tried it, but it still doesn't work...

I tried my own function CheckDate (removed some errors) and this makes sure that the date are correctly placed in the DB and also the calculation in the sql-queries are done correctly..

Here the function correct:

FUNCTION CheckDate(mydate)
'pre : IsDate(mydate)
'post :
IF DatePart("d", mydate) < 13 THEN
CheckDate = FormatDateTime(DatePart("m", mydate) & "-" & DatePart("d", mydate) & "-" & DatePart("yyyy", mydate), 2)
ELSE
CheckDate = FormatDateTime(mydate, 2)
END IF
END FUNCTION

Tnx for your support!

I used Date/Time, and typed in the format options: dd/mm/yyyy (Access 2000)

I use Acces 2003, maybe that's the difference. But then Bill did some weird updates...


Tnx again!

The easiest way to get around that is just input it into the database as a string or an integer then if you need to display it using asp you can just use the CDate function or build your own date string and store it as a variable. Access is quirky. I just usually set it up as basic as I can and do all the work on the code side. More flexible that way :)