Solved

Show all dates up to yesterday in SQL Query

Posted on 2015-01-08
4
117 Views
Last Modified: 2015-01-09
I want to show data for all dates up to yesterday at midnight. So I would be excluding any data for today.

Thanks for any assistance.
0
Comment
Question by:srodgers45
[X]
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
4 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40539564
select
*
from sometable
where somedatefield < cast(getdate() as date)
;


or

select
*
from sometable
where somedatefield < dateadd(day, datediff(day,0, getdate() ), 0)
;
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40539654
I agree on the above, see also this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

it's a bit sad that mssql server did implement getdate() as a date+time function, instead (like others) a date() (only date) and now() date + time ...
http://msdn.microsoft.com/en-us/library/ms188383.aspx
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40539935
Sigh, I utterly agree with you Guy!
But I suspect it came from Sybase (like most of initial mssql) but it's curious they haven't fixed it.
0
 

Author Closing Comment

by:srodgers45
ID: 40540825
Thanks for the help. Much appreciated...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A list of useful business intelligence software.
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

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