select date

[ZNB]

<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

[WolfShade]

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,

^ _ ^

[BKBK]

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

[ZNB]

        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

[haxtbh]

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

#DateSelect#"

Remove the quote marks at the end of this.

[EddieLotter]

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

[Dave Ferguson]

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

[ZNB]

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.

[EddieLotter]

What do you mean "nothing changes"?

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

Cheers

Eddie

[ZNB]

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

[EddieLotter]

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

[EddieLotter]

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

Cheers

Eddie

[ZNB]

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

[EddieLotter]

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

Cheers

Eddie

[ZNB]

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

[BKBK]

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

[ZNB]

<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

[ZNB]

Why was my application accepted and rejected?

THANK YOU

[BKBK]

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 #

[ZNB]

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 !

[BKBK]

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#

[ZNB]

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                     

[BKBK]

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.

[ZNB]

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