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
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,
^ _ ^
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))>
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
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.
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
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) >
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.
Copy link to clipboard
Copied
What do you mean "nothing changes"?
Please post the code where you expect to see a change.
Cheers
Eddie
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
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
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
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
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
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
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')>
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
Copy link to clipboard
Copied
Why was my application accepted and rejected?
THANK YOU
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 #
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 !
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#
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Résult :
HeureSelect : 10:00 heuredeb : 10 - mindeb : 00 AgendaHeureDeb : 1899-12-30 10:00:00.0
always the same problem !
I contact my web host
Thanks to all