Skip to main content
W
weezerboy
Inspiring
May 3, 2017
Answered

Date & Time

  • May 3, 2017
  • 3 replies
  • 813 views

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

This topic has been closed for replies.
Correct answer BKBK

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

3 replies

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
May 7, 2017

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

W
weezerboyAuthor
Inspiring
May 16, 2017

Thank you!

WolfShade
WolfShade
Legend
May 4, 2017

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,

^_^

S
Steve Sommers
Legend
May 4, 2017

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>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices