Solved

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

Posted on 2014-10-15
3
391 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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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