date variable in sql call

[paulcarey]

I've got this in the IIS forum as well but I thought someone here might be able to shed some light.

I have an Access97 database storing dates in UK format.
I have an IIS5 server running an ASP page that drags dates from the database and uses them in SQL calls to drag out more info.

The server is set in UK format but when the date variable is passed as part of the SQL call it seems to magically decide to be US. ie 01/11/02 becomes 11/01/02 in the sql call?!

This isn't a display issue as I have no problem formatting for display.

I seems seems solely to be an issue with the variable in the SQL call.

Anyone had anything similar?

 

[bdmoore]

Paul,

Try encoding your date variable using the following function before passing it to SQL:

function EncodeDateForSQL(dDateVar)
EncodeDateForSQL = "'" & Day(dDateVar) & "-" & _
MonthName(Month(dDateVar), true) & "-" & _
Year(dDateVar) & "'"
end function

This will return your date as a string variable which SQL Server will interpret correctly irrespective of it's date configuration.

Hope this helps

Ben