Date format causing problem

[gkeeara]

I have got the following query but when it actually inserts the date, the dates will be "08/01/2005". Month and day will be swapped if date is less than 12. I don't know it's happening. It's driving me crazy.

INSERT INTO P_Timesheet ( WeekNo, StaffID, StartTime, FinishTime, TotalHours, ADate, StaffActivityID, DateFilled, NotesByStaff )
VALUES (0, 'S24', #10:00:00 AM#, #11:00:00 AM#, 1, #1/08/2005#, 158, #21/08/2005#, '');

It will insert "8/01/2005" for the first date and "21/08/2005" for the second date. my date format is set to australia and i have set the same in my asp page as well. it is giving the same result even if i try direct from the query.

 

[neileg]

Look, don't shout at me for this, but SQL requires US date formats. This won't stop the data appearing correctly in Aussie format in your application, but 'under the bonnet' it's all US format. Remember that even when you think you are looking at data in the raw table view and the format is what you want, in reality it's a query result you are seeing and the conversion is done there.

 

[mresann]

In general, when you are hard-coding dates, use month names in the date field like "January" or "Jan". For instance, #1 Jan 2004# works just as well as #Jan 1 2004#. That way you will completely eliminate any date hangups.