Re: searching between 2 date fields with "where" in cfquery
> i have a table that has two date fields (dateinx and dateoutx) and i > need to find all the results for todays date that both fall between > and on that date for a given customer: cID. This should be fairly simple to add to your existing query... AND GETDATE() BETWEEN dateinx AND dateoutx > database is msSQL2005 ... i am using "smalldatetime" in > msSQL2005 and also would like to create a mask so that > only date: 1/1/2012 would be saved. > same for time: 1:30AM in a seperate field. msSQL is saving > now as 1/1/2012 01:30:00AM or some such... SQL Server has different data types for "date" and "time" which would store these values on their own (internally I believe they're still stored as a "smalldatetime" with static values for the unused portion. You can also look up the datepart() T-SQL function to break out a datetime into its component parts when needed. > WHERE (cID = #cID#) AND Also make sure you put a CFQUERYPARAM tag around that cID variable as well to prevent SQL injection, among other benefits. -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353335 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
searching between 2 date fields with "where" in cfquery
i have a table that has two date fields (dateinx and dateoutx) and i need to find all the results for todays date that both fall between and on that date for a given customer: cID. database is msSQL2005 cfserver is 7 if todays date is 12/2/2012examples of vialable entries: - dateinx=12/2/2012 and dateoutx = 12/5/2012 - dateinx=11/2/2012 and dateoutx = 12/2/2012 - dateinx=11/2/2012 and dateoutx = 12/25/2012 i am using "smalldatetime" in msSQL2005 and also would like to create a mask so that only date: 1/1/2012 would be saved. same for time: 1:30AM in a seperate field. msSQL is saving now as 1/1/2012 01:30:00AM or some such... cfquerey at this point looks like: SELECT * FROM schdl WHERE (cID = #cID#) AND tnx in advance andy ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353334 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: question about lists and CFMAIL
Matt, yep -- I scoped the variables. Seems to be working now. =) Eric -Original Message- From: Matt Quackenbush [mailto:quackfu...@gmail.com] Sent: Sunday, December 02, 2012 6:00 PM To: cf-talk Subject: Re: question about lists and CFMAIL Also, where is #FirstName# and #LastName# coming from? I am assuming they are also part of the query. If so, scope them as well. #NotPaid.FirstName[currentRow]# #NotPaid.LastName[currentRow]# On Sun, Dec 2, 2012 at 4:59 PM, Matt Quackenbush wrote: > Try scoping the to email: > > > > HTH > > > > On Sun, Dec 2, 2012 at 4:53 PM, Eric Bourland wrote: > >> >> >>> No need at all for the cfloop. When you provide the query to >> >>> cfmail it >> does the loop for you. >> >> Well, I believe you. At first I did try: >> >> > query="NotPaid" >> server="#REQUEST.MailingListServer#" >> from="i...@nnvawi.org" >> to="#NotPaid.UserEmail#" >> >> >> >> ... ColdFusion sends to only the first email address returned in >> query NotPaid; and it also populated the CFMAIL message with multiple >> values of #FirstName# and #LastName# -- for all records WHERE NotPaid=0. >> >> I am all for using the more efficient query attribute of CFMAIL. I am >> missing something. I will try it again and report exactly what happens. >> >> Eric >> >> >> >> ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:35 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: question about lists and CFMAIL
I take it all back. It is working: Why did it not work before? Very like, user error. =) Thank you all again for your help. This is a success. Eric ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353332 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: question about lists and CFMAIL
Also, where is #FirstName# and #LastName# coming from? I am assuming they are also part of the query. If so, scope them as well. #NotPaid.FirstName[currentRow]# #NotPaid.LastName[currentRow]# On Sun, Dec 2, 2012 at 4:59 PM, Matt Quackenbush wrote: > Try scoping the to email: > > > > HTH > > > > On Sun, Dec 2, 2012 at 4:53 PM, Eric Bourland wrote: > >> >> >>> No need at all for the cfloop. When you provide the query to cfmail it >> does the loop for you. >> >> Well, I believe you. At first I did try: >> >> > query="NotPaid" >> server="#REQUEST.MailingListServer#" >> from="i...@nnvawi.org" >> to="#NotPaid.UserEmail#" >> >> >> >> ... ColdFusion sends to only the first email address returned in query >> NotPaid; and it also populated the CFMAIL message with multiple values of >> #FirstName# and #LastName# -- for all records WHERE NotPaid=0. >> >> I am all for using the more efficient query attribute of CFMAIL. I am >> missing something. I will try it again and report exactly what happens. >> >> Eric >> >> >> >> ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353331 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: question about lists and CFMAIL
Try scoping the to email: HTH On Sun, Dec 2, 2012 at 4:53 PM, Eric Bourland wrote: > > >>> No need at all for the cfloop. When you provide the query to cfmail it > does the loop for you. > > Well, I believe you. At first I did try: > > query="NotPaid" > server="#REQUEST.MailingListServer#" > from="i...@nnvawi.org" > to="#NotPaid.UserEmail#" > > > > ... ColdFusion sends to only the first email address returned in query > NotPaid; and it also populated the CFMAIL message with multiple values of > #FirstName# and #LastName# -- for all records WHERE NotPaid=0. > > I am all for using the more efficient query attribute of CFMAIL. I am > missing something. I will try it again and report exactly what happens. > > Eric > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353330 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: question about lists and CFMAIL
>>> No need at all for the cfloop. When you provide the query to cfmail it does the loop for you. Well, I believe you. At first I did try: ... ColdFusion sends to only the first email address returned in query NotPaid; and it also populated the CFMAIL message with multiple values of #FirstName# and #LastName# -- for all records WHERE NotPaid=0. I am all for using the more efficient query attribute of CFMAIL. I am missing something. I will try it again and report exactly what happens. Eric ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353329 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: question about lists and CFMAIL
Eric, Take a look a the "Sending query-based email" on this page http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec14b97-7ffb.html You specify the query name in the query attribute and the email column as your "To:" attribute. Cfmail will then loop over the query and send a customized email to each record in the query. On Sun, Dec 2, 2012 at 5:45 PM, Matt Quackenbush wrote: > > No need at all for the cfloop. When you provide the query to cfmail it does > the loop for you. > > > On Sun, Dec 2, 2012 at 4:35 PM, Eric Bourland wrote: > > > > > Matt, thanks for that. I made progress. I read up on the query attribute > of > > CFMAIL. But after doing some research, I did find another way to make > this > > application work: CFLOOP. This seems to be working fine -- but do you > > recommend another way? I could not figure out how the query attribute of > > CFMAIL would help me in this case -- I am sure I am just missing > something > > very obvious. =) Thank you again for your help. Eric > > > > > > > > > > > > > > > > > server="#REQUEST.MailingListServer#" > > from="i...@nnvawi.org" > > to="#NotPaid.UserEmail#" > > subject="NNVAWI Membership Dues Reminder" > > username = "username" > > password = "#REQUEST.MailingListAdminPass#" > > SpoolEnable="No" > > type="html"> > > > > Greetings, #NotPaid.FirstName# #NotPaid.LastName#. > > > > This is a friendly reminder to pay your NNVAWI membership dues > for > > the current year. You may do so here at the secure authorize.net payment > > page: > > > > > > > > > > > > > > > > > > > > -Original Message- > > From: Matt Quackenbush [mailto:quackfu...@gmail.com] > > Sent: Sunday, December 02, 2012 4:16 PM > > To: cf-talk > > Subject: Re: question about lists and CFMAIL > > > > No, not on the right track. Look at the query attribute of . > That's > > what you want to use. > > > > > > > http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e08 > > 11cbec22c24-7f8e.html > > > > HTH > > > > > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353328 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: question about lists and CFMAIL
No need at all for the cfloop. When you provide the query to cfmail it does the loop for you. On Sun, Dec 2, 2012 at 4:35 PM, Eric Bourland wrote: > > Matt, thanks for that. I made progress. I read up on the query attribute of > CFMAIL. But after doing some research, I did find another way to make this > application work: CFLOOP. This seems to be working fine -- but do you > recommend another way? I could not figure out how the query attribute of > CFMAIL would help me in this case -- I am sure I am just missing something > very obvious. =) Thank you again for your help. Eric > > > > > > > >server="#REQUEST.MailingListServer#" > from="i...@nnvawi.org" > to="#NotPaid.UserEmail#" > subject="NNVAWI Membership Dues Reminder" > username = "username" > password = "#REQUEST.MailingListAdminPass#" > SpoolEnable="No" > type="html"> > > Greetings, #NotPaid.FirstName# #NotPaid.LastName#. > > This is a friendly reminder to pay your NNVAWI membership dues for > the current year. You may do so here at the secure authorize.net payment > page: > > > > > > > > > > -Original Message- > From: Matt Quackenbush [mailto:quackfu...@gmail.com] > Sent: Sunday, December 02, 2012 4:16 PM > To: cf-talk > Subject: Re: question about lists and CFMAIL > > No, not on the right track. Look at the query attribute of . That's > what you want to use. > > > http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e08 > 11cbec22c24-7f8e.html > > HTH > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353327 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: question about lists and CFMAIL
Matt, thanks for that. I made progress. I read up on the query attribute of CFMAIL. But after doing some research, I did find another way to make this application work: CFLOOP. This seems to be working fine -- but do you recommend another way? I could not figure out how the query attribute of CFMAIL would help me in this case -- I am sure I am just missing something very obvious. =) Thank you again for your help. Eric Greetings, #NotPaid.FirstName# #NotPaid.LastName#. This is a friendly reminder to pay your NNVAWI membership dues for the current year. You may do so here at the secure authorize.net payment page: -Original Message- From: Matt Quackenbush [mailto:quackfu...@gmail.com] Sent: Sunday, December 02, 2012 4:16 PM To: cf-talk Subject: Re: question about lists and CFMAIL No, not on the right track. Look at the query attribute of . That's what you want to use. http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e08 11cbec22c24-7f8e.html HTH ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353326 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: question about lists and CFMAIL
No, not on the right track. Look at the query attribute of . That's what you want to use. http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f8e.html HTH On Sun, Dec 2, 2012 at 3:03 PM, Eric Bourland wrote: > > ColdFusion 9.0.2 > MS SQL Server 2005 > > Greetings. I have a question about lists and CFMAIL. > > I need to send an email message to all email addresses in a data table that > are not marked "PaidDues". > > First, I query the data table to find all records with PaidDues = False (or > 0). > > > > SELECT UserID > ,FirstName > ,LastName > ,UserEmail > ,PaidDues > FROM #request.membersTable# > WHERE PaidDues = > > > Then I set up CFMAIL to send a reminder message to all UserEmail addresses > where PaidDues = 0. > > > > >server="#REQUEST.MailingListServer#" > from="Email Address" > to="#NotPaid.UserEmail#" > subject="NNVAWI Membership Dues Reminder" > username = "authorized user name" > password = "#REQUEST.MailingListAdminPass#" > SpoolEnable="Yes" > type="html"> > > Greetings, #NotPaid.FirstName# #NotPaid.LastName#. > > This is a friendly reminder to pay your NNVAWI membership dues for > the current year. You may do so here at the secure authorize.net payment > page: > >href="https://simplecheckout.authorize.net/payment/CatalogPayment.aspx > ">http > s://simplecheckout.authorize.net/payment/CatalogPayment.aspx > > > > > > The problem is that I need CFMAIL to process all of the records where > PaidDues = 0 -- I think, as a list. So that each email recipient is > identified correctly by FirstName, LastName; and the dues reminder message > goes out to all of the email addresses identified in the query. > > I think the list function is what I need -- but I am not sure how to > implement it. > > > http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSf01dbd23413dda0e1fb0 > 8f8f11feb424aa7-8000.html > > Am I going down the right path? Thank you for any advice. > > Eric > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353325 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
question about lists and CFMAIL
ColdFusion 9.0.2 MS SQL Server 2005 Greetings. I have a question about lists and CFMAIL. I need to send an email message to all email addresses in a data table that are not marked "PaidDues". First, I query the data table to find all records with PaidDues = False (or 0). SELECT UserID ,FirstName ,LastName ,UserEmail ,PaidDues FROM #request.membersTable# WHERE PaidDues = Then I set up CFMAIL to send a reminder message to all UserEmail addresses where PaidDues = 0. Greetings, #NotPaid.FirstName# #NotPaid.LastName#. This is a friendly reminder to pay your NNVAWI membership dues for the current year. You may do so here at the secure authorize.net payment page: https://simplecheckout.authorize.net/payment/CatalogPayment.aspx";>http s://simplecheckout.authorize.net/payment/CatalogPayment.aspx The problem is that I need CFMAIL to process all of the records where PaidDues = 0 -- I think, as a list. So that each email recipient is identified correctly by FirstName, LastName; and the dues reminder message goes out to all of the email addresses identified in the query. I think the list function is what I need -- but I am not sure how to implement it. http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSf01dbd23413dda0e1fb0 8f8f11feb424aa7-8000.html Am I going down the right path? Thank you for any advice. Eric ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353324 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
