I'm having an ASP,Access, inserting dates, nightmare!

[beaniebear]

I have an asp script that inserts dates from a form into an access database. The SQL insert is being written correctly. When the dates appear in the database they are in the format mm/dd/yyyy even though the format they were inputted in was dd/mm/yyyy. This is also affecting pulling information from the database when selecting data between two dates. If I enter between 01/11/2004 and 30/11/2004 it returns data from 11th January to 30th Nov.

Example of update query : Update [Development] set [DevDateActive]=#01/02/2003# where DevID=314

Example of select query : select * FROM Development WHERE Development.devDateActive between #01/11/2004# and #30/11/2004#

Finally I have checked that the regional settings on the server are set correctly.

Any help would be much appreciated.

 

[Lothario]

Try this:

select * FROM Development WHERE Development.devDateActive between CONVERT(varchar(10),#01/11/2004#,103) and CONVERT(varchar(10),#30/11/2004#,103)

-L

 

[beaniebear]

I tried this and I get the error:

Undefined function 'CONVERT' in expression

Do you know why I am experiencing this problem with date formats? Is it something that can be rectified or something that I'll needa work around for?

 

[Lothario]

try this:

select * FROM Development WHERE CDATE(Development.devDateActive) BETWEEN #01/11/2004# and #30/11/2004#

-L

 

[mwolf00]

Seems that the DB is expecting US format - not sure how to change it in Access, so maybe you need to work around it. Set the session.LCID to the output format you want and the US date will be converted for proper display.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[beaniebear]

I tried the revised sql and I get an error : invalid use of null.

I'm not sure what you mean by set session.lcid to to output format I want? Please could you expand. The format I require is dd/mm/yyyy

thanks

 

[mwolf00]

Search on session LCID

http://www.topxml.com/asp/session_object_property_lcid.asp

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[beaniebear]

I have added session.lcid=0409 to my code and this causes the page to stop displaying. There is no error other than 'The page cannot be displayed'.

 

[Lothario]

Go to internet options and uncheck the box stating "Show HTML Friendly error Messages"

that will show you exact error

-L

 

[beaniebear]

I was getting an error as the code i entered was wrong. I have now entered the correct code but i'm still having the same problem?

 

[inspiraller]

Try CONVERT(smalldatetime, '24/12/2004', 103)
This will switch the date around from UK to US style.

 

[mwolf00]

The CONVERT function will not work in Access, you should make sure the date is in the proper format when you insert it.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)