Solved

Show all dates up to yesterday in SQL Query

Posted on 2015-01-08
4
116 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 48

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 48

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Database Restore 2008 R2 1 27
deprecated mysql extensions 1 30
Why is this SQL bringing back extra rows? (parsing XML data) 4 39
Database Owner 3 21
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.
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.
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…

733 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