• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Date not the same as database!

Guest
Jun 28, 2012 Jun 28, 2012

Copy link to clipboard

Copied

Hi all

I changed my database field type from timestamp to date in mysql as I wanted to get rid of the time after the date. I changed the cfqueryparam cfsqltype to cf_sql_date and so far so good it now updates the correct date into the database without any time: 2012-06-30

When I output the value in my form however it shows a different date for some strange reason:

Fri Jun 29 23:00:00 GMT-0500 2012

This is the code I am using to output the date which has always worked fine showing the correct date. I have taken care to remove anything in the rest of my code that could affect the database result:

<cfformitem type="html" width="90" label="Present" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['MEETING']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'MEETING', MEETING.text);">

UPDATED: I did a cfdump on the query and the record in question shows this value in the cfdump but not on the page:

{ts '2012-06-30 00:00:00'}

Why am I getting the wrong date, do I need to dateformat MEETING.text or something? Quite why the date would be changed I don't understand at all. Thanks for any pointers!

Views

3.3K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Deleted User
Jul 05, 2012 Jul 05, 2012

Thanks to my hosts at hostek they found the solution!

I don't have thelink to the original post but you can find it by googling CFGRID Date Display Issues. Here are the solutions!!

1. When storing the date in the database, specify a time. In my case, setting the time to noon worked for me since my users are all located within the

continental US (therefore the offset would never be greater then 12 hours - side note that i'm too lazy to look up - can the offset ever be greater than 12

hours?). Here's

...

Votes

Translate

Translate
Guest
Jun 28, 2012 Jun 28, 2012

Copy link to clipboard

Copied

I'm just asking my hosts if it could be due to locale issues or something as the date Fri Jun 29 23:00:00 GMT-0500 2012 maybe a timestamp from the server, I don't really know clutching at straws a bit on this one but at least I'm learning!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 28, 2012 Jun 28, 2012

Copy link to clipboard

Copied

What TZ are you in?  Or, yeah, what TZ is the server in?

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jun 28, 2012 Jun 28, 2012

Copy link to clipboard

Copied

I'm in the Central Standard Time (CST) timezone and the server is in GMT.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 01, 2012 Jul 01, 2012

Copy link to clipboard

Copied

The time, Fri Jun 29 23:00:00 GMT-0500 2012, is the time at your locale(local time = Fri Jun 29 11:00 PM). The time, {ts '2012-06-30 00:00:00'}, simply tells us your server has an offset of +5 hours away from you (server local time = Sat Jun 30 04:00 AM).

These times are equivalent universally. However, you should be saving the server time, not the client time. Client times are arbitrary. For example, if I ran the same page from The Netherlands, I would have observed the time as Sat Jun 30 06:00 GMT+0200 2012.

My advice is that you save your datetimes in the database as UTC, and be done with all the relativity. As your server is in the GMT zone, that is what it is doing anyway. To obtain a client's local time, calculate it from the saved UTC and the client's time zone.

[Edited by BKBK]

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jul 01, 2012 Jul 01, 2012

Copy link to clipboard

Copied

Hi all,

Just FYI: A ticket was filed to add setTimeZone() (and possibly a THIS.timeZone per-app setting) to CF.  This would permit setting time zone at app-level (instead of requiring server-level config).  If you like this idea, you can vote for #3035908 here.  It was noted that Railo has setTimeZone().  Adobe ColdFusion 10 does not yet.

Thanks!,

-Aaron

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 01, 2012 Jul 01, 2012

Copy link to clipboard

Copied

Thanks for all the input, I have been reading a lot about this on various sites including ben nadell's, he has some interesting info there, the main problem seems to be with the way that coldfusion interprets dates which is different to the way that java does. 

I just tested the variables again on my (flash) cfform

  

<cfoutput>#now()#</cfoutput>

<cfscript>

tzoneObj    = createObject("java","java.util.TimeZone");

tzone       = tzoneObj.getTimeZone("Jamaica");

date_format = createObject("java","java.text.DateFormat").getDateTimeInstance();

date_format.setTimeZone(tzone);

writeoutput("<strong>Time Jamaica: </strong>"&#date_format.format(now())#);

</cfscript>

Results:

{ts '2012-07-01 14:01:57'}

Time Jamaica: Jul 1, 2012 1:01:57 PM

So I guess the answer must be as BKBK says, even though I thought my host's servers were in a different place that they appear to be!

I finally changed my flash cfform and cfgrid to html and hey presto it now works with the proper date, so I think I can conclude that in this case it was flash playing havoc with everything again for a change. Note to self: never use flash with coldfusion ever again.  I'll also vote for the timezone requirement that Aaron suggests, quite why coldfusion doesn't have it already is beyond me.

Also while we are on the subject why can't flash cfforms be seen in safari and opera? Yet another reason to ditch them. I haven't even tried looking on them in an ipad but I imagine they won't work there either due to the flash issue.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jul 01, 2012 Jul 01, 2012

Copy link to clipboard

Copied

Hi goodychurro1,

Those results indicate (assuming you ran that code right before posting the results):

1) the server's total UTC offset was -4

2) Jamaica's total UTC offset was -5

Possible reason for #1: Server's tz is EST (-5) and currently observes DST (+1) for a total offset of -4.

Reason for #2 is b/c Jamaica does not observe DST.  It is always -5.

So the result you saw for Jamaica appears to be correct.  I likely misunderstood the issue.  What were the results you were expecting?

As for the Flash Player issue, there is an update: http://forums.adobe.com/message/4511145

As for the <cfform format="flash" on iPad, currently <cfform format="flash" forms don't automatically fallback to HTML5.

Thanks,

-Aaron

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 01, 2012 Jul 01, 2012

Copy link to clipboard

Copied

Hi Aaron yes those were the results I was expecting, the problem is that the value in my database timestamp column is 2012-06-30 but when I show it in the flash cfform it is shown as 2012-06-29!

It works fine if the cfform is html. Strange stuff.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jul 01, 2012 Jul 01, 2012

Copy link to clipboard

Copied

goodychurro1 wrote:

Hi Aaron yes those were the results I was expecting, the problem is that the value in my database timestamp column is 2012-06-30 but when I show it in the flash cfform it is shown as 2012-06-29!

It works fine if the cfform is html. Strange stuff.

Hi goodychurro1,

Sorry I'm unable to reproduce this.  Steps I took:

1) In MSSQL Server 2008 R2, I created a new table named 'myTable' w/ a column named 'myDateField' of type 'date'

2) I populated the 1st row w/ the value 2012-06-30

3) I ran the following code:

<cfquery name="q">

SELECT myDateField FROM myTable

</cfquery>

<cfform>

  <cfinput type="text" name="myField" value="#q.myDateField[1]#" />

</cfform>

<cfform format="flash">

  <cfinput type="text" name="myField" value="#q.myDateField[1]#" />

</cfform>

Both input fields displayed {ts '2012-06-30 00:00:00'}

Thanks,

-Aaron

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 02, 2012 Jul 02, 2012

Copy link to clipboard

Copied

Here's the sql dump (I'm using mysql) and in the next post the .cfm:

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for city
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
  `countryId` int(1) DEFAULT NULL,
  `country` varchar(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of city
-- ----------------------------
INSERT INTO `city` VALUES ('1', 'Montego Bay');
-- ----------------------------
-- Table structure for ciudad
-- ----------------------------
DROP TABLE IF EXISTS `ciudad`;
CREATE TABLE `ciudad` (
  `CITYNOM` varchar(25) DEFAULT NULL,
  `CITYID` int(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`CITYID`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of ciudad
-- ----------------------------
INSERT INTO `ciudad` VALUES ('Kingston', '1');

-- ----------------------------
-- Table structure for company
-- ----------------------------
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
  `ENTID` int(3) NOT NULL AUTO_INCREMENT,
  `RANKING` int(3) DEFAULT NULL,
  `COMPANY` varchar(50) DEFAULT NULL,
  `FIRSTNAME` varchar(50) DEFAULT NULL,
  `ADDRESS` varchar(60) DEFAULT NULL,
  `CITY` int(3) DEFAULT NULL,
  `DEPT` varchar(18) DEFAULT NULL,
  `MOBILE` varchar(10) DEFAULT NULL,
  `EMAIL` varchar(48) DEFAULT NULL,
  `PHONE` varchar(65) DEFAULT NULL,
  `FAX` varchar(14) DEFAULT NULL,
  `SECTORS1` int(4) DEFAULT NULL,
  `POS` varchar(52) DEFAULT NULL,
  `COMMENTS` varchar(246) DEFAULT NULL,
  `STATE1` int(4) DEFAULT NULL,
  `MTGT` varchar(8) DEFAULT NULL,
  `STATEID` int(3) DEFAULT NULL,
  `DEPARTMENT1` int(3) DEFAULT NULL,
  `UPDATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `PRICE` int(5) DEFAULT NULL,
  `WHEN` int(1) DEFAULT '2',
  `ABUNDANCE` date DEFAULT NULL,
  PRIMARY KEY (`ENTID`)
) ENGINE=MyISAM AUTO_INCREMENT=793 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of company
-- ----------------------------
INSERT INTO `company` VALUES ('792', null, 'new one', 'test', null, '1', null, null, null, null, null, '1', null, null, '1', 'None', null, '1', '2012-07-02 19:13:59', null, '2', '2012-07-24');

-- ----------------------------
-- Table structure for division
-- ----------------------------
DROP TABLE IF EXISTS `division`;
CREATE TABLE `division` (
  `DEPARTMENTIDS` int(4) NOT NULL AUTO_INCREMENT,
  `DEPARTMENTNOM` varchar(30) NOT NULL,
  PRIMARY KEY (`DEPARTMENTIDS`)
) ENGINE=MyISAM AUTO_INCREMENT=52 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of division
-- ----------------------------
INSERT INTO `division` VALUES ('1', 'Finance');

-- ----------------------------
-- Table structure for groups
-- ----------------------------
DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `groupID` int(11) NOT NULL AUTO_INCREMENT,
  `groupName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`groupID`),
  UNIQUE KEY `groupID` (`groupID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of groups
-- ----------------------------

-- ----------------------------
-- Table structure for holidays
-- ----------------------------
DROP TABLE IF EXISTS `holidays`;
CREATE TABLE `holidays` (
  `holiday` date DEFAULT NULL,
  `holidayid` int(3) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`holidayid`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of holidays
-- ----------------------------
INSERT INTO `holidays` VALUES ('2012-12-25', '1');

-- ----------------------------
-- Table structure for industries
-- ----------------------------
DROP TABLE IF EXISTS `industries`;
CREATE TABLE `industries` (
  `SECTORSIDS` int(3) NOT NULL AUTO_INCREMENT,
  `SECTORSNOM` varchar(40) NOT NULL,
  PRIMARY KEY (`SECTORSIDS`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of industries
-- ----------------------------
INSERT INTO `industries` VALUES ('1', 'Cars');

-- ----------------------------
-- Table structure for sales
-- ----------------------------
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `SALESNOM` varchar(25) DEFAULT NULL,
  `SALESID` int(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`SALESID`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES ('CALL', '1');

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 02, 2012 Jul 02, 2012

Copy link to clipboard

Copied

Here's the .cfm, the offending field is ABUNDANCE and the cfgrid is also showing the date a day behind:

<cfquery name="qNames" datasource="tester">
    select * from company, industries, division
where company.SECTORS1 = industries.SECTORSIDS
<cfif structKeyExists(url,"sectorid")>
and industries.SECTORSIDS = #url.sectorid#</cfif>
and division.DEPARTMENTIDS = company.DEPARTMENT1
and company.WHEN = 2
ORDER BY company</cfquery>

<cfquery name="dNames" datasource="tester">
select DEPARTMENTIDS, DEPARTMENTNOM
    from division
order by DEPARTMENTNOM</cfquery>

<CFQUERY name="industries" datasource="tester">
SELECT * FROM industries
</CFQUERY>

<CFQUERY name="salesstep" datasource="tester">
SELECT * FROM sales
</CFQUERY>

<CFQUERY name="xity" datasource="tester">
SELECT * FROM ciudad
</CFQUERY>

<CFQUERY name="hols" datasource="tester">
SELECT * FROM holidays
</CFQUERY>

<!--- UPDATE QUERY --->
<cfif isDefined("form.UPDATEADDBTN")>
<cfif FORM.ENTID GTE 1>
<cfset isCOMPANYNull = iif(len(trim(form.COMPANY)) EQ 0, true, false)>
<cfset isSECTORS1Null = iif(len(trim(form.SECTORS1)) EQ 0, true, false)>
<cfset isRANKINGNull = iif(len(trim(form.RANKING)) EQ 0, true, false)>
<cfset isPRICENull = iif(len(trim(form.PRICE)) EQ 0, true, false)>
<cfset isDEPARTMENT1Null = iif(len(trim(form.DEPARTMENT1)) EQ 0, true, false)>
<cfset isFIRSTNAMENull = iif(len(trim(form.FIRSTNAME)) EQ 0, true, false)>
<cfset isPOSNull = iif(len(trim(form.POS)) EQ 0, true, false)>
<cfset isPHONENull = iif(len(trim(form.PHONE)) EQ 0, true, false)>
<cfset isEMAILNull = iif(len(trim(form.EMAIL)) EQ 0, true, false)>
<cfset isMOBILENull = iif(len(trim(form.MOBILE)) EQ 0, true, false)>
<cfset isADDRESSNull = iif(len(trim(form.ADDRESS)) EQ 0, true, false)>
<cfset isCITYNull = iif(len(trim(form.CITY)) EQ 0, true, false)>
<cfset isCOMMENTSNull = iif(len(trim(form.COMMENTS)) EQ 0, true, false)>
<cfset nxtstep = trim(form.ABUNDANCE)>
<cfif not isDate(nxtstep)>

<cfif DayOfWeek(Now()) eq 2>
<cfset nxtstep = dateAdd("d", 2, now())>
<cfelseif DayOfWeek(Now()) eq 3>
<cfset nxtstep = dateAdd("d", 2, now())>
<cfelseif DayOfWeek(Now()) eq 4>
<cfset nxtstep = dateAdd("d", 2, now())>
<cfelseif DayOfWeek(Now()) eq 5>
<cfset nxtstep = dateAdd("d", 4, now())>
<cfelseif DayOfWeek(Now()) eq 6>
<cfset nxtstep = dateAdd("d", 3, now())>
<cfelseif DayOfWeek(Now()) eq 7>
<cfset nxtstep = dateAdd("d", 2, now())>
<cfelseif DayOfWeek(Now()) eq 1>
<cfset nxtstep = dateAdd("d", 1, now())>
</cfif>

<cfelse>
<cfset nxtstep = trim(form.ABUNDANCE)>

</cfif>

<cfset isMEETINGTIMENull = iif(len(trim(form.MEETINGTIME)) EQ 0, true, false)>
<cfset isSTATE1Null = iif(len(trim(form.STATE1)) EQ 0, true, false)>

<CFQUERY name="updatecompany" datasource="tester">
update COMPANY
SET COMPANY = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.COMPANY)#" null="#isCOMPANYNull#" />,
   SECTORS1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.SECTORS1)#" null="#isSECTORS1Null#" />,
   RANKING = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.RANKING)#" null="#isRANKINGNull#" />,
   PRICE = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.PRICE)#" null="#isPRICENull#" />,
   DEPARTMENT1 = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.DEPARTMENT1)#" null="#isDEPARTMENT1Null#" />,
   FIRSTNAME = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.FIRSTNAME)#" null="#isFIRSTNAMENull#" />,
   POS = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.POS)#" null="#isPOSNull#" />,
   PHONE = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.PHONE)#" null="#isPHONENull#" />,
   EMAIL = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.EMAIL)#" null="#isEMAILNull#" />,
   MOBILE= <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.MOBILE)#" null="#isMOBILENull#" />,
   ADDRESS = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.ADDRESS)#" null="#isADDRESSNull#" />,
   CITY = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.CITY)#" null="#isCITYNull#" />,
   COMMENTS= <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.COMMENTS)#" null="#isCOMMENTSNull#" />,
   ABUNDANCE= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#nxtstep#" />,
   MTGT= <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.MEETINGTIME)#" null="#isMEETINGTIMENull#" />,
   STATE1= <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.STATE1)#" null="#isSTATE1Null#" />
WHERE ENTID = #FORM.ENTID#
</CFQUERY>
<CFLOCATION URL="tester.cfm?begin=1">
<cfelse>
<cfset isCOMPANYNull = iif(len(trim(form.COMPANY)) EQ 0, true, false)>
<cfset isSECTORS1Null   = iif(len(trim(form.SECTORS1)) EQ 0, true, false)>
<cfset isRANKINGNull = iif(len(trim(form.RANKING)) EQ 0, true, false)>
<cfset isPRICENull = iif(len(trim(form.PRICE)) EQ 0, true, false)>
<cfset isDEPARTMENT1Null = iif(len(trim(form.DEPARTMENT1)) EQ 0, true, false)>
<cfset isFIRSTNAMENull = iif(len(trim(form.FIRSTNAME)) EQ 0, true, false)>
<cfset isPOSNull = iif(len(trim(form.POS)) EQ 0, true, false)>
<cfset isPHONENull = iif(len(trim(form.PHONE)) EQ 0, true, false)>
<cfset isEMAILNull = iif(len(trim(form.EMAIL)) EQ 0, true, false)>
<cfset isMOBILENull = iif(len(trim(form.MOBILE)) EQ 0, true, false)>
<cfset isADDRESSNull = iif(len(trim(form.ADDRESS)) EQ 0, true, false)>
<cfset isCITYNull = iif(len(trim(form.CITY)) EQ 0, true, false)>
<cfset isCOMMENTSNull = iif(len(trim(form.COMMENTS)) EQ 0, true, false)>
<!--- dates --->
<cfset nxtstep = trim(form.ABUNDANCE)>
<cfif not isDate(nxtstep)>

<cfif DayOfWeek(Now()) eq 2>
<cfset nxtstep = dateAdd("d", 2, now())>
<cfelseif DayOfWeek(Now()) eq 3>
<cfset nxtstep = dateAdd("d", 2, now())>
<cfelseif DayOfWeek(Now()) eq 4>
<cfset nxtstep = dateAdd("d", 2, now())>
<cfelseif DayOfWeek(Now()) eq 5>
<cfset nxtstep = dateAdd("d", 4, now())>
<cfelseif DayOfWeek(Now()) eq 6>
<cfset nxtstep = dateAdd("d", 3, now())>
<cfelseif DayOfWeek(Now()) eq 7>
<cfset nxtstep = dateAdd("d", 2, now())>
<cfelseif DayOfWeek(Now()) eq 1>
<cfset nxtstep = dateAdd("d", 1, now())>
</cfif>

</cfif>

<cfset isMEETINGTIMENull = iif(len(trim(form.MEETINGTIME)) EQ 0, true, false)>
<cfset isSTATE1Null = iif(len(trim(form.STATE1)) EQ 0, true, false)>

<!--- ADD COMPANY QUERY --->
<CFQUERY name="addcompany" datasource="tester">
INSERT INTO COMPANY(
COMPANY,
SECTORS1,
RANKING,
PRICE,
DEPARTMENT1,
FIRSTNAME,
POS,
PHONE,
EMAIL,
MOBILE,
ADDRESS,
CITY,
COMMENTS,
ABUNDANCE,
MTGT,
STATE1

VALUES (
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.COMPANY)#" null="#isCOMPANYNull#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.SECTORS1)#" null="#isSECTORS1Null#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.RANKING)#" null="#isRANKINGNull#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.PRICE)#" null="#isPRICENull#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.DEPARTMENT1)#" null="#isDEPARTMENT1Null#" />,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.FIRSTNAME)#" null="#isFIRSTNAMENull#" />,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.POS)#" null="#isPOSNull#" />,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.PHONE)#" null="#isPHONENull#" />,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.EMAIL)#" null="#isEMAILNull#" />,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.MOBILE)#" null="#isMOBILENull#" />,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.ADDRESS)#" null="#isADDRESSNull#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.CITY)#" null="#isCITYNull#" />,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.COMMENTS)#" null="#isCOMMENTSNull#" />,
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#nxtstep#" />,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.MEETINGTIME)#" null="#isMEETINGTIMENull#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.STATE1)#" null="#isSTATE1Null#" />
)
</CFQUERY>
<CFLOCATION URL="tester.cfm?begin=1">
</cfif>
</cfif>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 

<title>Sales</title> 
</head>

<body style="margin-left:140px;">

<div>

<cfform name="sform" format="flash" skin="haloorange" width="1000" height="450">
<cfgrid name="UsersGrid"
           format="flash"
           query="qNames" width="910" rowheaders="No"
onchange="for (var i:Number = 0; i<STATE1.length; i++) {if (STATE1.getItemAt().data == UsersGrid.selectedItem.STATE1) STATE1.selectedIndex = i} for (var i:Number = 0; i<MEETINGTIME.length; i++) {if (MEETINGTIME.getItemAt().data == UsersGrid.selectedItem.MEETINGTIME) MEETINGTIME.selectedIndex = i} for (var i:Number = 0; i<CITY.length; i++) {if (CITY.getItemAt().data == UsersGrid.selectedItem.CITY) CITY.selectedIndex = i} for (var i:Number = 0; i<DEPARTMENT1.length; i++) {if (DEPARTMENT1.getItemAt().data == UsersGrid.selectedItem.DEPARTMENT1) DEPARTMENT1.selectedIndex = i}
for (var i:Number = 0; i<SECTORS1.length; i++) {if (SECTORS1.getItemAt().data == UsersGrid.selectedItem.SECTORS1) SECTORS1.selectedIndex = i}">

<cfgridcolumn name="RANKING" width="40"header="Rank">
<cfgridcolumn name="COMPANY" width="170" header="Company">
<cfgridcolumn name="FIRSTNAME" width="150" header="Contact Name">
<cfgridcolumn name="POS" width="160" header="Position">
<cfgridcolumn name="DEPARTMENTNOM" width="150" header="Department">
<cfgridcolumn name="SECTORSNOM" width="120" header="Sectors">
<cfgridcolumn name="ABUNDANCE" width="120" header="Action date">
   </cfgrid>

<cfformgroup type="tabnavigator" width="910">

<cfformgroup type="page" label="Details: : #qNames.RecordCount# Companies">
<!---#dateFormat(now(), 'mmm dd, yyyy')#--->
    <cfformgroup type="horizontal">
<cfinput type="text" name="COMPANY" label="Company" required="yes" width="200" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['COMPANY']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'COMPANY', COMPANY.text);">

<cfselect name="SECTORS1" width="120" size="1" label="Sector" required="yes" onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'SECTORS1', SECTORS1.selectedItem.data);">
<option></option><cfoutput query="industries"><option value="#SECTORSIDS#">#SECTORSNOM#</option></cfoutput>
</cfselect> 

<cfinput type="TEXT" name="RANKING" width="25" label="Ranking" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['RANKING']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'RANKING', RANKING.text);">

<cfinput type="TEXT" name="PRICE" width="35" label="Price" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['PRICE']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'PRICE', PRICE.text);">
 
<cfformitem type="html" width="90" label="Research" bind="<a href="?q={COMPANY.text}"">Google</a>">
   </cfformitem>
</cfformgroup>

<!--- SECOND --->
<cfformgroup type="horizontal">
<cfselect name="DEPARTMENT1" width="100" size="1" label="Department" required="yes" onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'DEPARTMENT1', DEPARTMENT1.selectedItem.data);">
<option></option><cfoutput query="dNames"><option value="#DEPARTMENTIDS#">#DEPARTMENTNOM#</option></cfoutput>
</cfselect> 

<cfinput type="text" name="FIRSTNAME" label="Name" required="yes" width="150" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['FIRSTNAME']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'FIRSTNAME', FIRSTNAME.text);">
<cfinput type="text" name="POS" label="Position" width="150" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['POS']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'POS', POS.text);">
<cfinput type="text" name="PHONE" label="Phone" width="150" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['PHONE']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'PHONE', PHONE.text);">
</cfformgroup>

<!--- THIRD--->
<cfformgroup type="horizontal">
<cfinput type="text" name="EMAIL" label="Email" width="150" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['EMAIL']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'EMAIL', EMAIL.text);">
<cfinput type="text" name="MOBILE" label="Mobile" width="120" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['MOBILE']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'MOBILE', MOBILE.text);">
<cfinput type="text" name="ADDRESS" label="Address" width="120" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ADDRESS']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ADDRESS', ADDRESS.text);">

<cfselect name="CITY" width="80" size="1" onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'CITY', CITY.selectedItem.data);">
<cfoutput query="xity"><option value="#CITYID#">#CITYNOM#</option></cfoutput>
</cfselect> 
</cfformgroup>


<!--- FOURTH --->
<cfformgroup type="horizontal">
<cftextarea name="COMMENTS" height="60" width="200" label="Comments" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['COMMENTS']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'COMMENTS', COMMENTS.text);" />

<cfinput type="DateField" name="ABUNDANCE" label="Action Date" width="100" >
  <!--- bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ABUNDANCE']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ABUNDANCE', ABUNDANCE.text);"--->
<cfformitem type="html" width="90" label="Present" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ABUNDANCE']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ABUNDANCE', ABUNDANCE.text);">
   </cfformitem>

<cfselect name="MEETINGTIME" width="90" label="Time"onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'MEETINGTIME', MEETINGTIME.selectedItem.data);">
             
   <cfset YourDateTime = "7:00AM">

<cfset startweek_dt = #timeFormat( YourDateTime,  'hh:mm tt')# >
<option value="None">None</OPTION>
<cfloop index="ii" from="1" to="23">
  <cfset startweek_dt = DateAdd('n',30, startweek_dt)>
                            <option value="<cfoutput>#timeFormat( startweek_dt,  'hh:mm tt')#</cfoutput>"><cfoutput>#timeFormat( startweek_dt,  'hh:mm tt')#</cfoutput></option>
  </cfloop>             </cfselect>

<cfselect name="STATE1" width="100" size="1" label="Action" onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'STATE1', STATE1.selectedItem.data);">
<cfoutput query="salesstep"><option value="#SALESID#">#SALESNOM#</option></cfoutput>
</cfselect> 

<cfinput type="hidden" name="ENTID" label="ENTID"
bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ENTID']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ENTID', ENTID.text);">
</cfformgroup>


</cfformgroup>
</cfformgroup>
    <cfformgroup type="horizontal">
  <cfformitem type="spacer"/>
<cfinput type="submit" name="DELCOM" value="delete" >
<cfinput type="submit" name="UPDATEADDBTN" value="UPDATE or ADD NEW">
<cfformitem type="spacer"/>
</cfformgroup>

</cfform>
<cfif isDefined("form.DELCOM")>
<CFQUERY name="deletecompany" datasource="tester">
DELETE FROM COMPANY
WHERE ENTID = #FORM.ENTID#
</CFQUERY>
<CFLOCATION URL="tester.cfm">
</cfif>


</body>
</html>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jul 02, 2012 Jul 02, 2012

Copy link to clipboard

Copied

Hi goodychurro1,

Please see the attached image.  Here are the steps I took:

1) Enabled the bind, by changing this:

<cfinput type="DateField" name="ABUNDANCE" label="Action Date" width="100" >

  <!--- bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ABUNDANCE']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ABUNDANCE', ABUNDANCE.text);"--->

to this:

<cfinput type="DateField" name="ABUNDANCE" label="Action Date" width="100" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ABUNDANCE']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ABUNDANCE', ABUNDANCE.text);">

2) I loaded the page and selected the 1st row in the grid.  The value for ABUNDANCE was correctly displayed as 2012-07-24 in the grid and in the datefield.

3) I reloaded the page and entered the following data: Company: "new two", Sector="cars", Department="Finance", Name="foo", Action Date=(I left this empty)

4) The value for ABUNDANCE was correctly displayed as 2012-07-05 in the database, and in the grid, and in the datefield.

5) I reloaded the page and entered the following data: Company: "new three", Sector="cars", Department="Finance", Name="bar", Action Date=(I selected July 5, 2012 from the date picker)

6) The value for ABUNDANCE was correctly displayed as 2012-07-05 in the database, and in the grid, and in the datefield.

Are these the expected results?

goodychurro1_01.jpg

Thanks,

-Aaron

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 02, 2012 Jul 02, 2012

Copy link to clipboard

Copied

goodychurro1 wrote:

<cfquery name="qNames" datasource="tester">
    select * from company, industries, division
where company.SECTORS1 = industries.SECTORSIDS
<cfif structKeyExists(url,"sectorid")>
and industries.SECTORSIDS = #url.sectorid#</cfif>
and division.DEPARTMENTIDS = company.DEPARTMENT1
and company.WHEN = 2
ORDER BY company</cfquery>

One more note.. I'd change this:

<cfif structKeyExists(url,"sectorid")>and industries.SECTORSIDS = #url.sectorid#</cfif>

to this:

<cfif structKeyExists(url,"sectorid") and isNumeric(url.sectorid)>and industries.SECTORSIDS = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(url.sectorid)#" /></cfif>

Thanks,

-Aaron

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jul 03, 2012 Jul 03, 2012

Copy link to clipboard

Copied

goodychurro1 wrote:

<cfformitem type="html" width="90" label="Research" bind="<a href="?q={COMPANY.text}"">Google</a>">
   </cfformitem>

Almost forgot..  I also had to remove that, since the syntax is wrong.  Example:

<!---<cfformitem type="html" width="90" label="Research" bind="<a href="?q={COMPANY.text}"">Google</a>">

   </cfformitem>--->

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 03, 2012 Jul 03, 2012

Copy link to clipboard

Copied

Hi Aaron

Thanks for checking it out, it is strange it doesn't happen to your page, what does your page server show for this?

<cfoutput>#now()#</cfoutput>

<cfscript>

tzoneObj = createObject("java","java.util.TimeZone");

tzone = tzoneObj.getTimeZone("Jamaica");

date_format = createObject("java","java.text.DateFormat").getDateTimeInstance();

date_format.setTimeZone(tzone);

writeoutput("<strong>Time Jamaica: </strong>"&#date_format.format(now())#);

</cfscript>

I think I have to change the date saved here to UTC_TIMESTAMP() or output it as UTC_TIMESTAMP():

  ABUNDANCE= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#nxtstep#" />,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jul 03, 2012 Jul 03, 2012

Copy link to clipboard

Copied

goodychurro1 wrote:

Hi Aaron

Thanks for checking it out, it is strange it doesn't happen to your page, what does your page server show for this?

Hi goodychurro1,

No problem.  And it displays this: {ts '2012-07-04 01:35:03'} Time Jamaica: Jul 4, 2012 12:35:03 AM

OS (local machine) tz is EST.  That's the correct time for me, as it was 1:35am EST when I ran that.  And that's the correct time in Jamaica, as they don't observe DST.

Thanks,

-Aaron

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 05, 2012 Jul 05, 2012

Copy link to clipboard

Copied

LATEST

Thanks to my hosts at hostek they found the solution!

I don't have thelink to the original post but you can find it by googling CFGRID Date Display Issues. Here are the solutions!!

1. When storing the date in the database, specify a time. In my case, setting the time to noon worked for me since my users are all located within the

continental US (therefore the offset would never be greater then 12 hours - side note that i'm too lazy to look up - can the offset ever be greater than 12

hours?). Here's an example:

<cfqueryparam value="#arguments.theDate# #timeformat(createtime(12, 00, 00), "hh:mm")#"

null="#not len(arguments.theDate)#"

cfsqltype="cf_sql_timestamp">

2. When querying the date, do a date add in your query to correct for the offset.

3. After querying, loop over the query column and do a date add to correct for the offset.

4. I suppose you could always convert the date to varchar in the query, but Flash still might recognize as a date? Also, sorting would probably be

broken...this one's probably not a good idea anyways.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation