Selecting data logged 1 day previous from 12:00 AM to 11:59 PM in SQL

Posted on 2014-10-15
Last Modified: 2014-11-12
I currently have query using ColdFusion begin and end date to set datetimestamp for begin and end date. Firstly, I'm not sure if this is correct with ColdFusion. If you are subtracting one day, does it already factor in the timestamp as 12:00 AM to 11:59 PM by default or does it need to be set as I've done below?

<cfset start= "#DateFormat(DateAdd('d',-1,Now()),'mm/dd/yyyy')# 00:00:00" />
<cfset end= "#DateFormat(DateAdd('d',-1,Now()),'mm/dd/yyyy')# 11:59:59" />

Open in new window

In my CFQUERY, this is what I'm using for selecting data with the above timestamps from previous day

WHERE table1.LDATETIME >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#CreateODBCDateTime(start)#" /> 
			AND table1.LDATETIME <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#CreateODBCDateTime(end)#" />

Open in new window

Alternatively, couldn't I just do this using TSQL with following condition?

WHERE table1.LDATETIME < (getdate() - 1)

Open in new window

Basically, if today is 10-15-2014, I would want query to return data that is 10-14-2014 12:00:00 AM to 10-14-2014 11:59:59 PM.
Question by:bman2011
  • 2
LVL 52

Accepted Solution

_agx_ earned 500 total points
ID: 40383507
>> If you are subtracting one day, does it already factor in the timestamp as 12:00 AM to 11:59 PM by default
No. Since you're using now(), it just deducts 24 hours from whatever the current time is at that moment. So if it's 6:00PM, the result will be 6PM of the previous day.

>>couldn't I just do this using TSQL with following condition?
Nope. SQL Server behaves the same way. It'll just subtract 1 day from the current time.  Though in later versions, you could probably CAST the result as a DATE (not the same as DATETIME)  and that would truncate the time portion.

>> return data that is 10-14-2014 12:00:00 AM to 10-14-2014 11:59:59 PM

A simpler way to do that is:

    <cfset today = now()>
    <cfset yesterday = dateAdd("d", -1, today)>
    WHERE table1.LDATETIME >= <cfqueryparam cfsqltype="cf_sql_date" value="#yesterday#" /> 
    AND       table1.LDATETIME < <cfqueryparam cfsqltype="cf_sql_date" value="#today#" />

Open in new window

By using CF_SQL_DATE (date only) the database ends up comparing only the date portion. It translates into this, which does the same thing as your current query.  Actually your current query could technically miss records that are a fraction of a second after 11:59:59, but that is unlikely.

     WHERE table1.LDATETIME >= '2014-10-14'
    AND       table1.LDATETIME < '2014-10-15'

I'm heading to sleep. Night!
LVL 48

Expert Comment

ID: 40383632
@_agx_ bravo! nicely done.

no points please.

without a doubt, no agrument, simply put:

for date ranges: always use >= with <

just like _agx_ did.

The problem many folks suffer with date ranges is that they assume that 1 second before midnight is OK as the end of day, unfortunately that isn't true. All sorts of problems can occur such as rounding, and the precision of this differs according to data type.
Be careful about rounding errors. Suppose you're using the DATETIME data type for your column, and your data has both date and time values that aren't necessarily midnight. You want to filter a period of date and time data such as January 2012. Some people use the following filter form:

WHERE col BETWEEN '20120101' AND '20120131 23:59:59.999'
The problem is that 999 as the millisecond unit isn't a multiplication of the precision unit for DATETIME, which is three and a third milliseconds. Therefore, the value gets rounded to the next midnight, and your range might end up including rows it isn't supposed to include. Some people "fix" this problem by using 997 in the millisecond unit, but what if at some point in the future you alter the type to one with finer precision? Therefore, the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan
LVL 52

Expert Comment

ID: 40384140
@PortletPaul - Thanks!

    >> Therefore, the value gets rounded to the next midnight

Ha, I didn't know that one. Yet another reason to use the ">= ... AND ... <" construct!

   >>  "This form works with all types and all precisions, regardless of whether the time part is applicable..."

Absolutely.  It makes working with date and datetime values (in any dbms) MUCH easier!

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Date Range Syntax Access 2003 10 45
Log Backup 2 13
Very interesting Access query problem. 13 41
How to simplify my SQL statement? 11 13
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now