Bl**dy date problems!

[DJCull]

I have table "games" and I am currently creating a page to update existing
records:

I have first page which lists games available for amending:


<cfquery name="open_games" datasource="PokerLeague">
SELECT gameID, gameReference, GameTitle, gameBuyin, gameDate, gameTime
FROM games
WHERE gameStatus = 'open'
ORDER BY gameDate, gameTime
</cfquery>

<cfoutput query="open_games">
<tr>
<td width="75" height="20" align="left" valign="middle"
style="padding-left: 15px">
<font class="dspTxt01">#gameReference#</font>
</td>
<td width="200" height="20" align="left" valign="middle">
<font class="dspTxt01">#gameTitle#</font>
</td>
<td width="75" height="20" align="left" valign="middle">
<font class="dspTxt01">#gameBuyin#</font>
</td>
<td width="75" height="20" align="left" valign="middle">
<font class="dspTxt01">#DateFormat(gameDate,
"dd/mm/yyyy")#</font>
</td>
<td width="75" height="20" align="left" valign="middle">
<font class="dspTxt01">#TimeFormat(gameTime,
"HH:mm")#</font>
</td>
<td width="100" height="20" align="left" valign="middle">
<a href="amend_game.cfm?id=#gameID#"
class="dspTxt02">Amend...</a>
</td>
<td width="100" height="20" align="left" valign="middle">
<a href="close_game.cfm?id=#gameID#"
class="dspTxt02">Close...</a>
</td>
<td width="100" height="20" align="left" valign="middle">
<a href="delete_game.cfm?id=#gameID#"
class="dspTxt02">Delete...</a>
</td>
</tr>
</cfoutput>



I then have 2nd page which dislays existing values in form for updating once
game selected on original page:


<cfquery name="selected_game" datasource="PokerLeague">
SELECT gameID, gameReference, GameTitle, gameBuyin, gameDate, gameTime
FROM games
WHERE gameID = #URL.id#
</cfquery>

<cfoutput query="selected_game" maxrows="1">
<cfform name="amend" action="amend_game_process.cfm">
<input type="hidden" name="gameID" value="#gameID#">
<table width="300" height="165">
<tr>
<td width="100" height="25" align="left"
valign="middle">
<font class="dspTxt01">Reference:</font>
</td>
<td width="200" height="25" align="left"
valign="middle">
<cfinput type="text" name="gameReference"
value="#gameReference#" class="dspTxt01" style="width: 200px" required="yes"
message="You must enter a Reference!">
</td>
</tr>
<tr>
<td width="100" height="25" align="left"
valign="middle">
<font class="dspTxt01">Title:</font>
</td>
<td width="200" height="25" align="left"
valign="middle">
<cfinput type="text" name="gameTitle"
value="#gameTitle#" class="dspTxt01" style="width: 200px" required="yes"
message="You must enter a Title!">
</td>
</tr>
<tr>
<td width="100" height="25" align="left"
valign="middle">
<font class="dspTxt01">Buy In:</font>
</td>
<td width="200" height="25" align="left"
valign="middle">
<cfinput type="text" name="gameBuyin"
value="#gameBuyin#" class="dspTxt01" style="width: 200px" required="yes"
message="You must enter a Buy In!">
</td>
</tr>
<tr>
<td width="100" height="25" align="left"
valign="middle">
<font class="dspTxt01">Date:</font>
</td>
<td width="200" height="25" align="left"
valign="middle">
<cfinput type="text" name="gameDate"
value="#DateFormat(gameDate, 'dd/mm/yyyy')#" class="dspTxt01" style="width:
200px" required="yes" message="You must enter a Date!">
</td>
</tr>
<tr>
<td width="100" height="25" align="left"
valign="middle">
<font class="dspTxt01">Time:</font>
</td>
<td width="200" height="25" align="left"
valign="middle">
<cfinput type="text" name="gameTime"
value="#TimeFormat(gameTime, 'HH:mm')#" class="dspTxt01" style="width: 200px"
required="yes" message="You must enter a Time!">
</td>
</tr>
<tr>
<td width="300" height="40" colspan="2"
align="right" valign="bottom">
<input type="submit" value="Amend..."
class="dspTxt01">
</td>
</tr>
</table>
</cfform>
</cfoutput>



Then final page that updates record:


<cfquery name="update_game" datasource="PokerLeague">
UPDATE games
SET gameReference = #FORM.gameReference#, gameTitle = '#FORM.gameTitle#',
gameBuyin = '#FORM.gameBuyin#', gameDate = #CreateODBCDate(FORM.gameDate)#,
gameTime = #createODBCTime(FORM.gameTime)#
WHERE gameID = #FORM.gameID#
</cfquery>



The problem I am getting is the date, once I have processed an amendment (with
or without a date change) the query update is processing the date in the wrong
format and so is entering into the record with the day and month numbers the
wrong way round, ie 10/07/2005 is ending up as 07/10/2005.

I am using MS Access as my database and the date field is saved as type
Date/Time with General Date format.

What should I do to retain the correct date format?

[cfmlgirl]

you not actually creating a date, you just inserting a actual date that games has been played. can you use this instead: #DateFormat(FORM.gameDate, "mm/dd/yy")#

[The ScareCrow]

This is because your using the "English" date format as opposed to the US
format that CF expects.

You can use setlocale to change this, but I have never got it to work as I
would like.

I now build the dates from the forms I have. You know that the date will be
entered in a certain format "dd/mm/yyyy"

CreateODBCDate(CreateDate(ListLast(FORM.gameDate, "/"), Mid(FORM.gameDate, 4,
2), ListFirst(FORM.gameDate, "/"))

cfmlgirl FYI
It is recommended that you never use date display function to insert into a
database, mainly I think because of the problems (as per this post)

Ken