Hi, I've got a problem with my database

I've got a couple of pages where you can insert dates into a database. Format: dd/mm/yyyy

Works great, if the day cannot be a month.

When I pick a day between the 1st and the 12th, the bigger number, whether it is the day, or the month, will become the month.

So 27/01/2004 will be inserted as 27/01/2004, but 03/02/2004 for instance, will be inserted as 02/03/2004

Even more strange, with these two dates, 03/02/2004 will be known as *before* 27/01/2004

I hope someone can help me with it.

Dates are always tricky. Best thing is to have special dropdowns for days/month/year and let the user pick the dates from them. You grab then the values and insert them in the db. This way you make sure the months and days are not mistaken.

When you retrieve them from db, use the datePart function to make sure you have the correct values.

At times I've had to disect the date from a known format then reassemble it again into a known format. Time is a bugger to work with as well, especially when you need a high degree of accuracy.

There was a question on something like this at sqlservercentral.com a couple weeks ago. It was about translating dates for a database that served UK and US offices.

Try setting your LCID explicitly. It might help out.

If you need some messy ugly horrible date processing code, let me know. I might have something that can help.

Bluefrog wrote: Try setting your LCID explicitly. It might help out.

Not all webhosters accepts other LCID than their own... I could not found a host to accept Romanian LCID (my case). So, this may not be the best solution. The only thing that realy helps is hard coding.

@ zaboss: If nothing else helps, I'll try that way, with the dropdowns. I'd prefer having it another way, but that might be the best solution.

@ Bluefrog: I've tried it with LCID. The problem is, it helps with getting the dates out of the database, but it's no good for inserting anything in it.
For the moment, when typing the date in the US-format in the administration panel, it goes into the database like that, and thanks to LCID, where users can see it, it shows in Belgian-format.

So, if anyone knows how I can make it so that I can insert anything in a format other than the host's default, it would be greatly apreciated.

You could use:

outputdate =  DatePart("yyyy", RS("mydatefield")) &"-"& DatePart("m", RS("mydatefield")) &"-"&DatePart("d", RS("mydatefield"))

insertdate = ""& DatePart("yyyy", Request.Form("MyInputForm")) &"-"& DatePart("m", Request.Form("MyInputForm")) &"-"& DatePart("d", Request.Form("MyInputForm")) &""

I use this as I need to have my dates mySQL compliant since I design apps that work with Access/SQL Server/MySQL, and mySQL is the most problematic with dates. It inserts and retrieve the dates in yyyy-mm-dd format, but, of course, you could adjust it to suit your needs.

I'll see if this can help me

But thanks for the help