Inserted date "dd/mm/yyyy" but when I view it it comes up "mm/dd/yyyy"

I've tried all that, (I didn't change the datatype in my database as it will effect other things)

if I select say the 19th May (19/05/2006) and insert it like that, it goes in fine, if the number in the month column is 12 or less then it inserts it mm/dd/yyyy but if its above 12 it inserts it dd/mm/yyyy which is how I want to be inserted into the database.

I did a test and inserted 09/05/2006 and when I looked in the database it stored it as 05/09/2006..
thanks

you may have to treat the date as a list and pull each value out into the createDate function.

myDate = createDate(listgetat(form.calendar,3,'/'),listgetat(form.calendar,2,'/'),listgetat(form.calendar,1,'/'))
This will create a date object and notice that createDate goes( year,month,day) using list get at will pull those values.

values('#myDate#')

I agree that having this field set to a string is a problem and you should probably, if possible, add a new column called Gig_Date_Dateformat and update that column with actual dates. Allowing you to keep the old column.

Thanks!!

Which bit of code would I need to put that into and how would I go about doing it?
Many thanks

The date being changed to data type date shouldn't affect other things any more than just doing a search for whereever you may insert or update this column and then changing the code slightly - it's just a matter of removing the ' around it.
I'm always amazed at the number of people who don't use a date field for date cause of "problems" when in reality you're setting yourself up for "problems" when you don't.

Now here is the clincher - let the db do whatever it wants with the formatting - as long as you know how to inerpret it.
Use jtreher's approach to the date list then use my #dateformat(Gig_Date,"d/mm/yyyy")# to display the date.
If the date is inserted to db - you can view the date in anyformat you want.

this will always work
1. because you fixed the location to us so coldfusion don't need to guess.
2. us format is always MM/DD/YYYY.
as cf server will swap the month and date if it become a valid date after swap.

good luck

Thanks all of you, sorry for been stupid but where does it go again?

<CFIF Session.LoggedIn NEQ "True">
  <CFLOCATION URL="/students/jasocke2/gigadded.cfm">
</CFIF>

(here??)-----myDate = createDate(listgetat(form.gig_date,3,'/'),listgetat(form.gig_date,2,'/'),listgetat(form.gig_date,1,'/'))
<cfquery datasource="jasvasquez-access">

INSERT INTO GIG(Gig_Day,Gig_Date,Venue,Other_bands,Additional_Info,Email_us)
VALUES('#myDate#',#createodbcdate(form.Gig_Date)#,'#FORM.Venue#','#FORM.Other_bands#','#FORM.Additional_Info#','#FORM.Email_us#')
</cfquery>

yes that's right

just put it anywhere before your cfquery will do

its saying... Variable MYDATE is undefined.
thanks

<cfset MYDATE  = "#ListGetAt(form.Gig_Date,2,"/")#/#ListGetAt(form.Gig_Date,1,"/")#/#ListGetAt(form.Gig_Date,3,"/")#">