Access/ASP date format problem

[danb]

Hi,

I have an access database which has a date field. I've entered all of the dates in valid UK format (dd/mm/yyyy). An example of the table looks like this:

Id| SeminarName | SeminarDate
1 | Introduction | 18/02/2006
2 | Other stuff | 19/02/2006



When I do:

SELECT * FROM tblSeminar WHERE SeminarDate=#18/02/2006#;


It doesn't return any records. If I remove the WHERE SeminarDate... it works fine, likewise if I use:

SELECT * FROM tblSeminar WHERE SeminarDate=#02/18/2006#;

So something is causing the date not to match.

I'm using <%Session.lcid = 2057%> at the top of all of my pages, and am hoping to keep all formatting in UK dd/mm/yyyy format.


Can anyone help here?? Thanks very much...

 

[Sergeant]

Rest assured, Access did not change the way it stored the date just because you told it you wanted to view it in a certain format.
The only thing I can think of is using DateSerial to build the date criteria???
I haven't had to handle this in ASP before, I suggest you post it on www.aspmessageboard.com as they know what they're talking about.

 

[ReAn]

If you're having issues with date formats, the sloppy fix (but works well) is as follows:

SELECT *, Day(SeminarDate) as SeminarDay, Month(SeminarDate) as SeminarMonth, Year(SeminarDate) as SeminarYear FROM tblSeminar WHERE SeminarDay = 18 AND SeminarMonth = 2 AND SeminarYear = 2006;

It works great for individual dates, but not great for ranges/inequalities.

 

[RuralGuy]

Here's a link I think you will find useful.

 

[danb]

Thanks, yeah, I hacked about with my queries and found that inserting any date record into Access has to be done in US format, despite Access displaying the information in UK format. <sarcasm>Very handy</sarcasm>.

I think that link is along the same lines RuralGuy, thanks.

 

[RuralGuy]

That's correct. It sounds like you got things working. Excellent.