Highlighted

select date

Participant ,
Jun 10, 2017

Copy link to clipboard

Copied

<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

Views

1.2K

Likes

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

select date

Participant ,
Jun 10, 2017

Copy link to clipboard

Copied

<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

Views

1.2K

Likes

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
Jun 10, 2017 0
LEGEND ,
Jun 11, 2017

Copy link to clipboard

Copied

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,

^ _ ^

Likes

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
Reply
Loading...
Jun 11, 2017 0
Adobe Community Professional ,
Jun 11, 2017

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Jun 11, 2017 0
Participant ,
Jun 13, 2017

Copy link to clipboard

Copied

        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

Likes

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
Reply
Loading...
Jun 13, 2017 0
Advocate ,
Jun 13, 2017

Copy link to clipboard

Copied

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

#DateSelect#"

Remove the quote marks at the end of this.

Likes

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
Reply
Loading...
Jun 13, 2017 0
Advocate ,
Jun 13, 2017

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Jun 13, 2017 0
Engaged ,
Jun 13, 2017

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Jun 13, 2017 0
Participant ,
Jun 14, 2017

Copy link to clipboard

Copied

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.

Likes

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
Reply
Loading...
Jun 14, 2017 0
Advocate ,
Jun 14, 2017

Copy link to clipboard

Copied

What do you mean "nothing changes"?

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

Cheers

Eddie

Likes

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
Reply
Loading...
Jun 14, 2017 0
Participant ,
Jun 14, 2017

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Jun 14, 2017 0
Advocate ,
Jun 14, 2017

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Jun 14, 2017 0
Advocate ,
Jun 14, 2017

Copy link to clipboard

Copied

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

Cheers

Eddie

Likes

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
Reply
Loading...
Jun 14, 2017 0
Participant ,
Jun 15, 2017

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Jun 15, 2017 0
Advocate ,
Jun 15, 2017

Copy link to clipboard

Copied

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

Cheers

Eddie

Likes

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
Reply
Loading...
Jun 15, 2017 0
Participant ,
Jun 16, 2017

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Jun 16, 2017 0
Adobe Community Professional ,
Jun 17, 2017

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Jun 17, 2017 0
Participant ,
Jun 20, 2017

Copy link to clipboard

Copied

<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

               

Likes

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
Reply
Loading...
Jun 20, 2017 0
Participant ,
Jun 21, 2017

Copy link to clipboard

Copied

Why was my application accepted and rejected?

THANK YOU

Likes

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
Reply
Loading...
Jun 21, 2017 0
Adobe Community Professional ,
Jun 21, 2017

Copy link to clipboard

Copied

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 #

Likes

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
Reply
Loading...
Jun 21, 2017 0
Participant ,
Jun 22, 2017

Copy link to clipboard

Copied

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 !

Likes

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
Reply
Loading...
Jun 22, 2017 0
Adobe Community Professional ,
Jun 22, 2017

Copy link to clipboard

Copied

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#

Likes

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
Reply
Loading...
Jun 22, 2017 0
Participant ,
Jun 22, 2017

Copy link to clipboard

Copied

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                     

Likes

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
Reply
Loading...
Jun 22, 2017 0
Adobe Community Professional ,
Jun 22, 2017

Copy link to clipboard

Copied

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.

Likes

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
Reply
Loading...
Jun 22, 2017 0
ZNB LATEST
Participant ,
Jun 23, 2017

Copy link to clipboard

Copied

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:0...

always the same problem !


I contact my web host


Thanks to all

Likes

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
Reply
Loading...
Jun 23, 2017 0