17 Replies Latest reply: Jul 5, 2012 3:56 PM by goodychurro1 RSS

    Date not the same as database!

    goodychurro1 Community Member

      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!

        • 1. Re: Date not the same as database!
          goodychurro1 Community Member

          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!

          • 2. Re: Date not the same as database!
            Adam Cameron. Community Member

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

             

            --

            Adam

            • 3. Re: Date not the same as database!
              goodychurro1 Community Member

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

              • 4. Re: Date not the same as database!
                BKBK CommunityMVP

                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]

                • 5. Re: Date not the same as database!
                  itisdesign CommunityMVP

                  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

                  • 6. Re: Date not the same as database!
                    goodychurro1 Community Member

                    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.

                    • 7. Re: Date not the same as database!
                      itisdesign CommunityMVP

                      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

                      • 8. Re: Date not the same as database!
                        goodychurro1 Community Member

                        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.

                        • 9. Re: Date not the same as database!
                          itisdesign CommunityMVP

                          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

                          • 10. Re: Date not the same as database!
                            goodychurro1 Community Member

                            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');

                            • 11. Re: Date not the same as database!
                              goodychurro1 Community Member

                              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([i]).data == UsersGrid.selectedItem.STATE1) STATE1.selectedIndex = i} for (var i:Number = 0; i<MEETINGTIME.length; i++) {if (MEETINGTIME.getItemAt([i]).data == UsersGrid.selectedItem.MEETINGTIME) MEETINGTIME.selectedIndex = i} for (var i:Number = 0; i<CITY.length; i++) {if (CITY.getItemAt([i]).data == UsersGrid.selectedItem.CITY) CITY.selectedIndex = i} for (var i:Number = 0; i<DEPARTMENT1.length; i++) {if (DEPARTMENT1.getItemAt([i]).data == UsersGrid.selectedItem.DEPARTMENT1) DEPARTMENT1.selectedIndex = i}
                              for (var i:Number = 0; i<SECTORS1.length; i++) {if (SECTORS1.getItemAt([i]).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>

                              • 12. Re: Date not the same as database!
                                itisdesign CommunityMVP

                                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

                                • 13. Re: Date not the same as database!
                                  Aaron Neff Community Member

                                  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

                                  • 14. Re: Date not the same as database!
                                    itisdesign CommunityMVP

                                    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>--->

                                    • 15. Re: Date not the same as database!
                                      goodychurro1 Community Member

                                      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#" />,

                                       

                                      • 16. Re: Date not the same as database!
                                        itisdesign CommunityMVP

                                        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

                                        • 17. Re: Date not the same as database!
                                          goodychurro1 Community Member

                                          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.