Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
Solved
Returning a query with #now()#
Posted on 2013-01-03
Experts-
I'm having some trouble returning results from a query. I am using SQL server 2008 and the field (enterDate) is set as a dateTime in the table. When I run the query below, it does not return any records although there are records in the DB that should be returned. Any suggestions would be appreciated. EnterDate field in the db looks like this....
2013-01-03 09:51:09.613
<cfquery name="getBillable" datasource="#application.d
select *
from prospects
where email = '#form.email#'
and enterDate = #dateFormat(now(),'yyyy-mm
</cfquery>
Thanks
Nick
I'm having some trouble returning results from a query. I am using SQL server 2008 and the field (enterDate) is set as a dateTime in the table. When I run the query below, it does not return any records although there are records in the DB that should be returned. Any suggestions would be appreciated. EnterDate field in the db looks like this....
2013-01-03 09:51:09.613
<cfquery name="getBillable" datasource="#application.d
select *
from prospects
where email = '#form.email#'
and enterDate = #dateFormat(now(),'yyyy-mm
</cfquery>
Thanks
Nick
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
- +1
5 Comments
Active 4 days ago
why not set the date as a variable before the query then pass the variable into the query.
Then you will need the exact time as well as the date!!
Otherwise:
select *
from prospects
where email = '#form.email#'
and enterDate > [original date] AND enterDate < [orginal date plus 1 day]
Or you can use the SQL BETWEEN statement.
Otherwise:
select *
from prospects
where email = '#form.email#'
and enterDate > [original date] AND enterDate < [orginal date plus 1 day]
Or you can use the SQL BETWEEN statement.
Active 4 days ago
Once you do that I would use a comparative instead of "=" as that is expecting the exact same value, if you are planning to check for date time to the second then be sure to assign that in your statement as you are asking for
Row value = Variable
If you are looking for a period then you are better off looking in a window vs time to the second.
Row value = Variable
If you are looking for a period then you are better off looking in a window vs time to the second.
Active today
> and enterDate > [original date] AND enterDate < [orginal date plus 1 day]
Small typo. I think he meant ">= [original date]" rather than "> [original date]"
Edit: That approach works well. Using cfqueryparam it is a simple as:
WHERE enterDate >= <cfqueryparam value="#now()#" cfsqltype="cf_sql_date">
AND enterDate < <cfqueryparam value="#dateAdd('d', 1, now())#" cfsqltype="cf_sql_date">
...
Small typo. I think he meant ">= [original date]" rather than "> [original date]"
Edit: That approach works well. Using cfqueryparam it is a simple as:
WHERE enterDate >= <cfqueryparam value="#now()#" cfsqltype="cf_sql_date">
AND enterDate < <cfqueryparam value="#dateAdd('d', 1, now())#" cfsqltype="cf_sql_date">
...
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented.
Please …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month8 days, 13 hours left to enroll
721 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.