Highlighted

Date & Time

Participant ,
May 03, 2017

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

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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

Views

475

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Date & Time

Participant ,
May 03, 2017

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

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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

Views

476

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 03, 2017 0
Advocate ,
May 03, 2017

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 03, 2017 0
LEGEND ,
May 04, 2017

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 04, 2017 0
Adobe Community Professional ,
May 07, 2017

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2017 0
Participant ,
May 15, 2017

Copy link to clipboard

Copied

Thank you!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 15, 2017 0