Solved

Show all dates up to yesterday in SQL Query

Posted on 2015-01-08
4
108 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
  • 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 142

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now