Thread: Date Formatting In SQL

I have the following code which works fine, with the exception of the formatting of the date (English formatting).

I have tried changing the formatting, but if the date is, for example, 09/12/2011 (9th December) it will input 12/09/2011.
If the date is above 11/12/2011 (11th December) it enters the date fine.

Does anyone know of a fix for this.

Sub StartEndDates()

Dim sqlQry As String
Dim yesterdayDate As String
Dim rsTemp As New ADODB.Recordset

    sqlQry = "UPDATE WeekID_tbl SET WeekID_tbl.[Date] = Format(Now()-1,'dd/mm/yyyy')"
    DoCmd.RunSQL sqlQry

    'Gets the date for the first day of the reporting week
    rsTemp.Open "SELECT Min(Calendar_tbl.[Date]) AS MinOfWeekDate " & _
          "FROM Calendar_tbl INNER JOIN WeekID_tbl ON Calendar_tbl.[WeekID] = WeekID_tbl.[WeekID] " & _
          "HAVING Calendar_tbl.[WeekID]=WeekID_tbl.[WeekID];", _
          CurrentProject.Connection, adOpenStatic, adLockReadOnly

    sqlQry = "UPDATE WeekID_tbl SET WeekID_tbl.[FirstDayOfWeek] = Format(#" & rsTemp!MinOfWeekDate & "#,'dd/mmm/yy');"
    DoCmd.RunSQL sqlQry

    rsTemp.Close

    'Gets the date for the last day of the reporting week
    rsTemp.Open "SELECT Max(Calendar_tbl.[Date]) AS MaxOfWeekDate " & _
          "FROM Calendar_tbl INNER JOIN WeekID_tbl ON Calendar_tbl.[WeekID] = WeekID_tbl.[WeekID] " & _
          "HAVING Calendar_tbl.[WeekID]=WeekID_tbl.[WeekID];", _
          CurrentProject.Connection, adOpenStatic, adLockReadOnly

    sqlQry = "UPDATE WeekID_tbl SET WeekID_tbl.[LastDayOfWeek] = Format(#" & rsTemp!MaxOfWeekDate & "#,'ddd/mmm/yy');"
    DoCmd.RunSQL sqlQry

    rsTemp.Close

End Sub

Thanks Ron, you gave me an idea, it's Access i'm using by the way.
I sort of did the opposite, left the data as it is and changed the formatting and now it inserts it in 'rest of the world' format.

sqlQry = "UPDATE WeekID_tbl SET WeekID_tbl.[FirstDayOfWeek] = Format(#" & rsTemp!MinOfWeekDate & "#,'mm/dd/yy');"