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

select date

Contributor ,
Jun 10, 2017 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.6K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 11, 2017 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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 11, 2017 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))>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 13, 2017 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 13, 2017 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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 13, 2017 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jun 13, 2017 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) >

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 14, 2017 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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 14, 2017 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 14, 2017 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 14, 2017 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 14, 2017 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 15, 2017 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 15, 2017 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 16, 2017 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 17, 2017 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')>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 20, 2017 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

               

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

Why was my application accepted and rejected?

THANK YOU

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 21, 2017 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 #

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 22, 2017 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 !

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 22, 2017 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#

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 22, 2017 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                     

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 22, 2017 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.

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

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