23 Replies Latest reply on Jun 23, 2017 8:27 AM by ZNB

    select date

    ZNB Level 1

      <cfset DateSelect=16/07/2017>

      <cfset NumJour=#Dayofweek(DateSelect -1)#>

      <cfset Dim=#dateadd('D',-NumJour,DateSelect)#>

       

       

       

      <CFQUERY name="MajAgenda2" datasource="#session.dbname#">

            SELECT AgendaObjet FROM Agenda WHERE AgendaDateDeb=#DateSelect# GROUP BY AgendaObjet

      </CFQUERY>

       

       

      <CFOUTPUT>MajAgenda2.recordcount :# MajAgenda2.recordcount# </CFOUTPUT>

       

       

      <CFOUTPUT query="MajAgenda2">

              <cfset AgendaObjet="#AgendaObjet#">

      </CFOUTPUT>

       

       

       

      Hello,
      I try to select an item for a date with no result
      Thank you for your help
      cordially

        • 1. Re: select date
          WolfShade Level 4

          Hello,

           

          The first thing you need to do is to CFSET the date within quotes:

          <cfset DateSelect = "16/07/2017" />

           

          Then use CFQUERYPARAM and CREATEODBCDATE in your query:

          <cfquery>
               SELECT AgendaObjet 
               FROM Agenda 
               WHERE AgendaDateDeb=<cfqueryparam value="#CreateODBCDate(DateSelect)#" /> 
               GROUP BY AgendaObjet
          </cfquery>
          

           

          HTH,

           

          ^ _ ^

          • 2. Re: select date
            BKBK Adobe Community Professional & MVP

            ZNB  wrote

             

            <cfset DateSelect=16/07/2017>

            <cfset dateSelect = createDate(2017,6,17)>

             

            <cfset NumJour=#Dayofweek(DateSelect -1)#>

             

            If you wish to subtract one day, the use

            <cfset NumJour=dayOfWeek(dateadd('D',-1,DateSelect))>

            • 3. Re: select date
              ZNB Level 1

                      I now have an error message: "Syntax error in string in query expression 'CalendarDateDeb = # 11/6/2017 00: 00: 00 #" AND sitename =' SoBoat 'GROUP BY AgendaObject'. "With the following code:

               

                   <CFOUTPUT>

                     <cfset DateSelect="17/06/2017">

                     <!--- <cfset DateSelect="#Dateformat(dateadd('D',m,Dim),"DD/MM/YYYY")#">  --->

                     <cfset DateSelect=#createODBCDate(DateSelect)#>

                     <cfset an="#Year(DateSelect)#"><cfset mois="#Month(DateSelect)#"><cfset jour="#Day(DateSelect)#">

                     an : #an# - mois : #mois# - jour : #jour#

                     <cfset dateSelect = #createDate(an,mois,jour)#>

                     dateselect : #dateselect# --->

                     </CFOUTPUT>

                     

                     <CFQUERY name="MajAgenda2" datasource="#session.dbname#">

                      SELECT

                       AgendaObjet

                      FROM Agenda

                      WHERE

                       AgendaDateDeb=#DateSelect#"

                      AND sitename='#Session.site#'

                      GROUP BY AgendaObjet

                     </CFQUERY>

                     <!--- <CFOUTPUT>MajAgenda2.recordcount :# MajAgenda2.recordcount# - DateSelect : #DateSelect#</CFOUTPUT> --->

                     <CFOUTPUT query="MajAgenda2">

                      <cfset AgendaObjet="#AgendaObjet#">

                     </CFOUTPUT>

               

              I think it comes from dates in French!
              Thank you for your help

              • 4. Re: select date
                haxtbh Level 4

                Its more likely the rogue " you have in the query.

                 

                #DateSelect#"

                 

                Remove the quote marks at the end of this.

                • 5. Re: select date
                  EddieLotter Level 3

                  ZNB  wrote

                   

                          I now have an error message: "Syntax error in string in query expression 'CalendarDateDeb = # 11/6/2017 00: 00: 00 #" AND sitename =' SoBoat 'GROUP BY AgendaObject'. "With the following code:

                   

                       <CFOUTPUT>

                         <cfset DateSelect="17/06/2017">

                         <!--- <cfset DateSelect="#Dateformat(dateadd('D',m,Dim),"DD/MM/YYYY")#">  --->

                         <cfset DateSelect=#createODBCDate(DateSelect)#>

                         <cfset an="#Year(DateSelect)#"><cfset mois="#Month(DateSelect)#"><cfset jour="#Day(DateSelect)#">

                         an : #an# - mois : #mois# - jour : #jour#

                         <cfset dateSelect = #createDate(an,mois,jour)#>

                         dateselect : #dateselect# --->

                         </CFOUTPUT>

                         

                         <CFQUERY name="MajAgenda2" datasource="#session.dbname#">

                          SELECT

                           AgendaObjet

                          FROM Agenda

                          WHERE

                           AgendaDateDeb=#DateSelect#"

                          AND sitename='#Session.site#'

                          GROUP BY AgendaObjet

                         </CFQUERY>

                         <!--- <CFOUTPUT>MajAgenda2.recordcount :# MajAgenda2.recordcount# - DateSelect : #DateSelect#</CFOUTPUT> --->

                         <CFOUTPUT query="MajAgenda2">

                          <cfset AgendaObjet="#AgendaObjet#">

                         </CFOUTPUT>

                   

                  I think it comes from dates in French!
                  Thank you for your help

                  Replace the entire block of code that you show above with the code shown below

                   

                  <cfset DateSelect = createDate(2017,6,17)>
                  <CFQUERY name="MajAgenda2" datasource="#session.dbname#">
                    SELECT
                      AgendaObjet
                    FROM Agenda
                    WHERE
                      AgendaDateDeb = <cfqueryparam value="#DateSelect#" CFSQLType="CF_SQL_DATE">
                      AND sitename = '#Session.site#'
                    GROUP BY AgendaObjet
                  </CFQUERY>
                  <cfset AgendaObjet = MajAgenda2.AgendaObjet>
                  

                   

                  Cheers

                  Eddie

                  • 6. Re: select date
                    Dave Ferguson Level 3

                    Also, just an FYI.  If you are not outputting or quoting the code you don't need to put "#" signs around it.  For example...

                     

                     

                    <cfset DateSelect=#createODBCDate(DateSelect)#>

                    can be

                    <cfset DateSelect = createODBCDate(DateSelect)>

                     

                     

                    <cfset an="#Year(DateSelect)#"><cfset mois="#Month(DateSelect)#"><cfset jour="#Day(DateSelect)#">

                    can be

                    <cfset an= Year(DateSelect) ><cfset mois= Month(DateSelect) ><cfset jour= Day(DateSelect) >

                    • 7. Re: select date
                      ZNB Level 1

                      Thank you for all your advice.
                      For dates, it works!
                      On the other hand, I have problems now with the hours:
                        Selection date: 10:00:00
                      Date display: 1899-12-30 10: 00: 00.0.
                      Despite a TimeDate: <cfset CalendarHeureDeb = "# Timeformat (AgendaHeureDeb, 'HH: MM') #">, nothing changes!
                      Thank you for you precious help.

                      • 8. Re: select date
                        EddieLotter Level 3

                        What do you mean "nothing changes"?

                         

                        Please post the code where you expect to see a change.

                         

                        Cheers

                        Eddie

                        • 9. Re: select date
                          ZNB Level 1

                          my code :

                           

                          <CFOUTPUT query="MajAgenda2">

                                 AgendaHeureDeb : #AgendaHeureDeb# (1899-12-30 10: 00: 00.0)

                                 <cfset AgendaHeureDeb="#Timeformat(AgendaHeureDeb,'HH:MM')#">

                                 AgendaHeureDeb : #AgendaHeureDeb# (1899-12-30 10: 00: 00.0)

                          </CFOUTPUT>

                          Tank you

                          • 10. Re: select date
                            EddieLotter Level 3

                            You're overwriting your date value with a string value. Did you intend to do that?

                             

                            Try the following as an alternative:

                            <CFOUTPUT query="MajAgenda2">
                              AgendaHeureDeb : #AgendaHeureDeb#
                              <cfset AgendaHeureDebFormatted = Timeformat(AgendaHeureDeb,'HH:MM')>
                              AgendaHeureDebFormatted : #AgendaHeureDebFormatted#
                            </CFOUTPUT>
                            

                             

                            Cheers

                            Eddie

                            • 11. Re: select date
                              EddieLotter Level 3

                              Furthermore, check the documentation on allowable masks for the TimeFormat() function. If you want minutes, use "nn" not "MM" as the mask.

                               

                              Cheers

                              Eddie

                              • 12. Re: select date
                                ZNB Level 1

                                Still no change!
                                In Access, time = 10:00
                                <Cfset CalendarHeureDeb = "# Timeformat (AgendaHeureDeb, 'HH: nn') #">
                                Result: 1899-12-30 10: 00: 00.0
                                I do not understand !
                                Thank you for your help

                                • 13. Re: select date
                                  EddieLotter Level 3

                                  Please show your test code as it is now. It looks like you are still using # symbols where you should not be.

                                   

                                  Cheers

                                  Eddie

                                  • 14. Re: select date
                                    ZNB Level 1

                                    No changes!
                                    In Access, time = 10:00
                                    My code:

                                    <CFQUERY name="MajAgenda2" datasource="#session.dbname#">

                                            SELECT

                                             AgendaObjet,

                                             AgendaHeureDeb

                                            FROM Agenda

                                            WHERE

                                             AgendaDateDeb=#DateSelect#

                                            AND sitename='#Session.site#'

                                            GROUP BY AgendaHeureDeb,AgendaObjet

                                           </CFQUERY>

                                     

                                           <CFOUTPUT query="MajAgenda2">

                                           AgendaHeureDeb1 : #AgendaHeureDeb# - HeureSelect : #HeureSelect#

                                           <cfset AgendaHeureDeb="Timeformat(AgendaHeureDeb,'HH:nn')">

                                           AgendaHeureDeb : #AgendaHeureDeb#

                                           <A href="Agenda_Maj.cfm?Mode=creation&DateSelect=#Dateformat(dateadd('D',m,Dim),"DD/MM/YY")# &HeureSelect=#HeureSelect#"> </A> 

                                         

                                          <CFIF MajAgenda2.recordcount EQ "0" AND HeureSelect EQ "AgendaHeureDeb">

                                              ...

                                    Result: 1899-12-30 10: 00: 00.0

                                    Thank you for your help

                                    • 15. Re: select date
                                      BKBK Adobe Community Professional & MVP

                                      You must represent dates as datetime objects, not as strings. To repeat,

                                       

                                      <cfset dateSelect = createDate(2017,6,17)>

                                       

                                      <cfset NumJour=#Dayofweek(DateSelect -1)#>

                                       

                                      If you wish to subtract one day, the use

                                      <cfset NumJour=dayOfWeek(dateadd('D',-1,DateSelect))>

                                       

                                      <Cfset CalendarHeureDeb = "# Timeformat (AgendaHeureDeb, 'HH: nn') #">

                                      <cfset AgendaHeureDeb = parseDateTime(AgendaHeureDeb)> <!--- To ensure datetime object --->

                                      <cfset CalendarHeureDeb = timeformat(AgendaHeureDeb,'HH:nn')>

                                      • 16. Re: select date
                                        ZNB Level 1

                                        <CFQUERY name="MajAgenda2" datasource="#session.dbname#">

                                                SELECT

                                                 AgendaObjet,

                                                 AgendaHeureDeb

                                                FROM Agenda

                                                WHERE

                                                 AgendaDateDeb=#DateSelect#

                                                AND sitename='#Session.site#'

                                                GROUP BY AgendaHeureDeb,AgendaObjet

                                        </CFQUERY>

                                               <!--- <CFOUTPUT>MajAgenda2.recordcount :# MajAgenda2.recordcount# </CFOUTPUT> --->

                                               <CFOUTPUT query="MajAgenda2">

                                                HeureSelect : #HeureSelect#

                                                <cfset heuredeb="#Hour(AgendaHeureDeb)#"><cfset mindeb="#Minute(AgendaHeureDeb)#">

                                                heuredeb : #heuredeb# - mindeb : #NumberFormat(mindeb,'00')#

                                                <cfset AgendaHeureDeb=#heuredeb#&":"&#mindeb#>

                                                <cfset AgendaHeureDeb = parseDateTime(AgendaHeureDeb)>

                                                <cfset AgendaHeureDeb = timeformat(AgendaHeureDeb,'HH:nn')>

                                                AgendaHeureDeb : #AgendaHeureDeb#

                                         

                                        Result :

                                             HeureSelect : 10:00

                                             heuredeb : 10 - mindeb : 00

                                             AgendaHeureDeb : 1899-12-30 10:00:00.0  

                                         

                                        ?????

                                        Thank you for your help

                                                       

                                        • 17. Re: select date
                                          ZNB Level 1

                                          Why was my application accepted and rejected?

                                          THANK YOU

                                          • 18. Re: select date
                                            BKBK Adobe Community Professional & MVP

                                            Strange. In your code it appears that ColdFusion fails to see the timeformat function. What happens when you replace the 3 lines with the ones I gave:

                                             

                                            <cfset AgendaHeureDeb = parseDateTime(AgendaHeureDeb)> <!--- To ensure datetime object --->

                                            <cfset CalendarHeureDeb = timeformat(AgendaHeureDeb,'HH:nn')>

                                            CalendarHeureDeb : #CalendarHeureDeb #

                                            • 19. Re: select date
                                              ZNB Level 1

                                              Indeed, the "timeformat" function does not seem to work!

                                              By replacing "agenda" with "calendar", I have an improvement
                                              : I have the time but not the minutes.

                                               

                                               

                                              Here's the code:

                                               

                                              HeureSelect : #HeureSelect#

                                              <cfset heuredeb="#Hour(AgendaHeureDeb)#"><cfset mindeb="#Minute(AgendaHeureDeb)#">

                                              heuredeb : #heuredeb# - mindeb : #NumberFormat(mindeb,'00')#

                                              <cfset AgendaHeureDeb=#heuredeb#&":"&#mindeb#>

                                              <cfset AgendaHeureDeb = parseDateTime(AgendaHeureDeb)> <!--- To ensure datetime object --->

                                              <cfset CalendarHeureDeb = timeformat(AgendaHeureDeb,'HH:nn')>

                                              AgendaHeureDeb : #CalendarHeureDeb#

                                               

                                              Tank you !

                                              • 20. Re: select date
                                                BKBK Adobe Community Professional & MVP

                                                Sorry to hear that. You might have a bug, but it's too early to say.

                                                 

                                                Now time for a workaround. Why don't you try this:

                                                 

                                                <cfset AgendaHeureDeb = parseDateTime(AgendaHeureDeb)>   

                                                <cfset simpleDateFormat = createobject("java", "java.text.SimpleDateFormat").init("HH:MM")>

                                                <cfset AgendaHeureDeb = simpleDateFormat.format(AgendaHeureDeb)>

                                                AgendaHeureDeb : #AgendaHeureDeb#

                                                • 21. Re: select date
                                                  ZNB Level 1

                                                  hello

                                                   

                                                  result :

                                                   

                                                  HeureSelect : 10:30 heuredeb : 10 - mindeb : 00 AgendaHeureDeb : 1899-12-30 10:00:00.0  

                                                   

                                                  If it's an Adobe problem, like I have a hosted account, what should I ask the host?

                                                   

                                                  Tank you                     

                                                  • 22. Re: select date
                                                    BKBK Adobe Community Professional & MVP

                                                    Sorry, there was an error in post. Please read 

                                                     

                                                    <cfset simpleDateFormat = createobject("java", "java.text.SimpleDateFormat").init("hh:mm")>

                                                     

                                                    If you still fail to get 10:00 you should contact the host.

                                                    • 23. Re: select date
                                                      ZNB Level 1

                                                      Résult :

                                                       

                                                      HeureSelect : 10:00 heuredeb : 10 - mindeb : 00 AgendaHeureDeb : 1899-12-30 10:00:00.0

                                                               http://www.zennetbuilder.com/admin/Agenda_Maj.cfm?Mode=creation&DateSelect=20/06/17&HeureS elect=10:00

                                                       

                                                      always the same problem !


                                                      I contact my web host


                                                      Thanks to all