Date & Time

[weezerboy]

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

[Steve Sommers]

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>

[WolfShade]

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,

^_^

[BKBK · Accepted solution]

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

[weezerboy]

Thank you!