• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Date & Time

Participant ,
May 03, 2017 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

Views

664

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
community guidelines

correct answers 1 Correct answer

Community Expert , May 07, 2017 May 07, 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  BATC

...

Votes

Translate

Translate
Advocate ,
May 03, 2017 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>

Votes

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
community guidelines
LEGEND ,
May 04, 2017 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,

^_^

Votes

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
community guidelines
Community Expert ,
May 07, 2017 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)#

Votes

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
community guidelines
Participant ,
May 15, 2017 May 15, 2017

Copy link to clipboard

Copied

LATEST

Thank you!

Votes

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
community guidelines
Resources
Documentation