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

Posted on 2014-10-15
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 52

Accepted Solution

_agx_ earned 2000 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 49

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

777 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