Copy link to clipboard
Copied
If I need to get a date & time value for a SQL query for 5:00 PM yesterday, and 5 PM today how do I do that
I want to get all of the records between 5 PM yesterday and 5 PM today
select *
from MYTABLE
WHERE BATCH_DATE > (yesterday at 5PM ) AND BATCH_DATE < (todayat 5PM)
order by id desc
Ideally, you should solve this using the datetime functions of your database engine. In any case, a possible answer using ColdFusion is
<cfset currentYear = year(now())>
<cfset currentMonth = month(now())>
<cfset currentDay = day(now())>
<!--- Today 5 PM --->
<cfset today5PM = createDateTime(currentYear, currentMonth, currentDay, 17, 0, 0)>
<!--- Yesterday 5 PM, obtained by removing 1 day from today --->
<cfset yesterday5PM = dateAdd("d", -1, today5PM)>
Then, in your query, add:
WHERE BATC
...Copy link to clipboard
Copied
There are several ways, the simplest probably being:
<cfset variables.dttm1 = createDateTime(2017,5,2,17,0,0) /> |
<cfset variables.dttm2 = createDateTime(2017,5,3,17,0,0) />
<cfquery ...>
SELECT | ||
* | ||
FROM | ||
MYTABLE | ||
WHERE | ||
BATCH_DATE > <cfqueryparam value="#variables.dttm1#" cfsqltype="cf_sql_timestamp" /> | ||
AND BATCH_DATE <- <cfqueryparam value="#variables.dttm2#" cfsqltype="cf_sql_timestamp" /> | ||
ORDER BY | ||
ID DESC |
</cfquery>
Copy link to clipboard
Copied
First, if I may, I'd recommend against using SELECT * in any query. This forces the database to reference a table to get the column names for the table(s), then apply all those column names to the query. Always specify precisely which columns you need; using the * in your SELECT is not only inefficient because it has to get the column names first, but also because unless you NEED every column of data, you are retrieving information that you don't need which uses more network bandwidth than is necessary.
You don't state which flavor of SQL you are using (MS SQL; MySQL; Oracle; FoxPro; etc.) If you are using MySQL, MS SQL or Oracle, you can use BETWEEN, which is inclusive (returns values that include range values, not just those in between. ie 'between 3 and 6' will return 3, 4, 5, and 6, not just 4 and 5.)
Going off of Steve Sommers' example:
SELECT colA, colB, colC, colG, colZ
FROM MYTABLE
WHERE BATCH_DATE BETWEEN <cfqueryparam value="#variables.dttm1#" cfsqltype="cf_sql_timestamp" /> AND <cfqueryparam value="#variables.dttm2#" cfsqltype="cf_sql_timestamp" />
ORDER BY colA
HTH,
^_^
Copy link to clipboard
Copied
Ideally, you should solve this using the datetime functions of your database engine. In any case, a possible answer using ColdFusion is
<cfset currentYear = year(now())>
<cfset currentMonth = month(now())>
<cfset currentDay = day(now())>
<!--- Today 5 PM --->
<cfset today5PM = createDateTime(currentYear, currentMonth, currentDay, 17, 0, 0)>
<!--- Yesterday 5 PM, obtained by removing 1 day from today --->
<cfset yesterday5PM = dateAdd("d", -1, today5PM)>
Then, in your query, add:
WHERE BATCH_DATE > #createODBCDateTime(yesterday5PM)# AND BATCH_DATE < #createODBCDateTime(today5PM)#
Copy link to clipboard
Copied
Thank you!