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

I have recently Inserted a Gig of a band into a database and I have a pop-up calender where the date comes from, when I click on the date I want it inserts it in to the box beside it...
for example if i selected 8th May, in the box it would put in 08/05/2006, when I submit the querey, it goes into the database fine, but when I "View all gigs" the Gig comes up as 05/08/2006.
Is it somthing to do with the insert statement?
INSERT INTO GIG(Gig_Day,Gig_Date,Venue,Other_bands,Additional_Info,Email_us)

does the "Gig_date" need to be formatted somehow?
Who is Participating?
pcaiooConnect With a Mentor Commented:
try this as the database always save your datetime format in us format you can either change your location by
<cfset local = #SetLocale("English (US)")#>
<cfset Gig_DateN = "#ListGetAt(form.Gig_Date,2,"/")#/#ListGetAt(form.Gig_Date,1,"/")#/#ListGetAt(form.Gig_Date,3,"/")#">
dgrafxConnect With a Mentor Commented:
First off - use a data type of date for your date field in your database.
To insert to db then use #createodbcdate(form.Gig_Date)# with no quotes
You may have to format your form date like "mm/dd/yyyy" before inserting.
Second - is this an access db in non-US formatting? Like UK for ex?
Anyway - when you "View all Gigs" to format the date as you want it - just use #dateformat(Gig_Date,"d/mm/yyyy")#
jasocke2Author Commented:
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..
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.


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.
jasocke2Author Commented:

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.

jtreherConnect With a Mentor Commented:
/// Which bit of code would I need to put that into and how would I go about doing it?

Just put that above your database insert and instead of inserting the form.gig_date, you'll insert #myDate#

myDate = createDate(listgetat(form.gig_date,3,'/'),listgetat(form.gig_date,2,'/'),listgetat(form.gig_date,1,'/'))
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
jasocke2Author Commented:
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">

(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)
yes that's right
just put it anywhere before your cfquery will do
jasocke2Author Commented:
its saying... Variable MYDATE is undefined.
<cfset MYDATE  = "#ListGetAt(form.Gig_Date,2,"/")#/#ListGetAt(form.Gig_Date,1,"/")#/#ListGetAt(form.Gig_Date,3,"/")#">
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.