Solved

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

Posted on 2014-10-15
3
409 Views
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.
0
Comment
Question by:bman2011
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
_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!
0
 
LVL 48

Expert Comment

by:PortletPaul
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
0
 
LVL 52

Expert Comment

by:_agx_
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!
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Disable TLS1.0 on Win 2012 server 7 55
SQL to JSON 14 30
sql query 5 38
SQL Syntax 6 27
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 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