Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

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

Hi,
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)
VALUES('#FORM.Gig_Day#','#FORM.Gig_Date#','#FORM.Venue#','#FORM.Other_bands#','#FORM.Additional_Info#','#FORM.Email_us#')

does the "Gig_date" need to be formatted somehow?
thanks
0
jasocke2
Asked:
jasocke2
  • 5
  • 4
  • 2
  • +1
3 Solutions
 
dgrafxCommented:
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")#
0
 
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..
thanks
0
 
jtreherCommented:
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.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
jasocke2Author Commented:
Thanks!!

Which bit of code would I need to put that into and how would I go about doing it?
Many thanks
0
 
dgrafxCommented:
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.

0
 
jtreherCommented:
/// 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,'/'))
0
 
pcaiooCommented:
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)")#>
then
<cfset Gig_DateN = "#ListGetAt(form.Gig_Date,2,"/")#/#ListGetAt(form.Gig_Date,1,"/")#/#ListGetAt(form.Gig_Date,3,"/")#">
#CreateODBCDATETIME("#Gig_DateN#")#
0
 
pcaiooCommented:
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
0
 
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">
</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>
0
 
pcaiooCommented:
yes that's right
0
 
pcaiooCommented:
just put it anywhere before your cfquery will do
0
 
jasocke2Author Commented:
its saying... Variable MYDATE is undefined.
thanks
0
 
pcaiooCommented:
<cfset MYDATE  = "#ListGetAt(form.Gig_Date,2,"/")#/#ListGetAt(form.Gig_Date,1,"/")#/#ListGetAt(form.Gig_Date,3,"/")#">
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now