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

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.
bman2011Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
>> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
@_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
_agx_Commented:
@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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.