Date format issues after moving SQL.

Hi
(Aus store where we use DD/MM/YYYY)

Got a small problem.
Our site used to be on a US date formatted SQL, and thus we used PC inbuilt MM/DD/YYYY to DD/MM/YYYY conversion.

When we moved our SQL to out new server which uses Aus date format, all the dates changed to DD/MM/YYYY, which is perfect.

Everything in the store works perfectly, (All dates are displayed correctly) except the help desk, where the dates are wrong, and it crashes when posting after the 12th of the month. (For obvious reasons)

Now it seems everything is using DD/MM/YYYY format and its saving in the DB as DD/MM/YYYY, but the helpdesk is using MM/DD/YYYY and causing problems.

Our SQL_Format file is on '0' If we try and change it to 1 as recommend our site constantly crashes with incorrect date format problems.

Anybody else had this issue? Obviously not US stores but anybody else from Aussie land with Local SQLs.

Thanks
Rob

Hi Again,
Thought I'd give an update to our problem. Also submitted a ticket but we worked it out eventually.

What happened is this.
For some strange reason, the constant scDB=SQL wasnt being passed from pages that referenced checkdate.asp, or was being cleared out.
As a consequence, the helpdesk was trying to use MDB query strings. For another strange reason, the query worked on the 1-12th of every month, which lead me to believe it was a date problem, when in fact it was a constant passing issue.

Our fix was just to delete the MDB queries from the "If SQL then SQL else MDB" to just use SQL.

Really cant find a reason why this problem didnt happen on our previous host and surfaced the second when we went live with the new one. All can put it down to is they must have something preinstalled on our server that kept clearing the constant scDB=SQL when a (now) date process was called.
Who knows... But at least now its fixed.

The problem was also evident on the home page with the New Arrivals, Best sellers. The site was wrapping the right hand side of the frame into the best sellers, because the SQL statement wasn’t being closed at line 459

if scdb="SQL" then y="'"
else y="#"
end if

Changed to

if scdb="SQL" then y="'"
else y="'"
end if

Anyway, all good now. Strange indeed....



Edited by RobertZ - 23-August-2010 at 6:31pm

Hi Sean
Thanks for that.

Eventually after all our problems with this, its actually an EI coding problem with the checkdate function.

It displays the incorrect date format, (When DD/MM/YYYY) inside the helpdesk. (Problem been replicated on EI demo stores). Under some circumstances, like ours, the problem does something to also damage the sql constant passed on those pages.

Anyway, EI have it on there list of fixes on the next update.. Hopefully