Skip to main content
ZNB
Inspiring
Jun 11, 2017 12:27 AM UTC
Question

select date

<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

    This topic is closed to new replies. Start a new post to keep the conversation going.

    23 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    Jun 23, 2017 3:55 AM UTC

    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.

    Translate
    ZNBAuthor
    Inspiring
    Jun 23, 2017 3:27 PM UTC

    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&HeureSelect=10:00

    always the same problem !


    I contact my web host


    Thanks to all

    Translate
    Dave Ferguson
    Dave Ferguson
    Participating Frequently
    Jun 13, 2017 5:06 PM UTC

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

    Translate
    ZNBAuthor
    Inspiring
    Jun 14, 2017 4:28 PM UTC

    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.

    Translate
    EddieLotter
    EddieLotter
    Inspiring
    Jun 14, 2017 4:34 PM UTC

    What do you mean "nothing changes"?

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

    Cheers

    Eddie

    Translate
    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    Jun 12, 2017 5:28 AM UTC

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

    Translate
    ZNBAuthor
    Inspiring
    Jun 13, 2017 11:03 AM UTC

            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

    Translate
    haxtbh
    Inspiring
    Jun 13, 2017 1:53 PM UTC

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

    #DateSelect#"

    Remove the quote marks at the end of this.

    Translate
    WolfShade
    WolfShade
    Legend
    Jun 11, 2017 10:41 PM UTC

    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,

    ^ _ ^

    Translate
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices