Solved

SELECT * where TODAY minus ONE DAY

Posted on 2007-03-28
8
19,884 Views
Last Modified: 2012-08-14
Okay so If I do...

SELECT * from orders

the 'OrderDate' column is formatted like '2007-03-28 16:29:35.000'

but if I do

SELECT * from Orders where OrderDate = '2007-03-28' then nothing is displayed.

What I ultimatley want is to do

SELECT * from Orders where OrderDate = DATEADD(day, -1, GETDATE())

But no results are returned even if there is data there.

Please somone help.  Need this workin for tonight.

Thanks,

Andrew.
0
Comment
Question by:andrewmilner
8 Comments
 
LVL 11

Accepted Solution

by:
Ved Prakash Agrawal earned 200 total points
ID: 18808823


SELECT * from Orders where CONVERT(varchar(10),OrderDate,120) = '2007-03-28'
0
 
LVL 3

Assisted Solution

by:cognos79
cognos79 earned 100 total points
ID: 18808824
SELECT * from Orders
where datediff(day,OrderDate,GETDATE()) = 1
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 18808828
You may consider this for previous day records...

SELECT * from Orders where convert(varchar(8), OrderDate, 112) = convert(varchar(8), DATEADD(day, -1, GETDATE()), 112)...

Hope this helps...
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 11

Assisted Solution

by:Ved Prakash Agrawal
Ved Prakash Agrawal earned 200 total points
ID: 18808890
SELECT * from Orders
 where OrderDate >=CONVERT(varchar(10),getdate(),120) AND
      OrderDate < CONVERT(varchar(10),DATEADD(day, 1, GETDATE()),120)


you can use any one query
0
 
LVL 10

Assisted Solution

by:lahousden
lahousden earned 100 total points
ID: 18808910
You are being caught in the datetime trap - you always need to consider the fact that the time is tacked on.  To accomlish what you intended by

SELECT * from Orders where OrderDate = '2007-03-28' then nothing is displayed.

you would need to run

SELECT * from Orders
where OrderDate >= '2007-03-28'
and OrderDate < '2007-03-29'

(so that all the times are considered, not only those orders that were created at exactly midnight).

To accomplish your ultimate goal:

SELECT * from Orders
where OrderDate >= convert (datetime, convert (nvarchar, GetDate(), 101), 101) - 1
and OrderDate < convert (datetime, convert (nvarchar, GetDate(), 101), 101)

0
 

Author Comment

by:andrewmilner
ID: 18808959
Thanks guys, very speed responses there.  Very impressed.

I think I like this one best

SELECT * from Orders
where datediff(day,OrderDate,GETDATE()) = 1

as there's never going to be any orders made by people with time machines.
0
 
LVL 5

Expert Comment

by:Atropa
ID: 18809004
I think that the easiest solution would be:

SELECT * from Orders where OrderDate = Convert(VarChar(10, DATEADD(day, -1, GETDATE()), 101)

It would be like saying:

SELECT * from Orders where OrderDate = '3/28/2007'

Which would return the corect results.

0
 

Expert Comment

by:vsrob
ID: 37913144
FOR SSIS one day Minus Current Date, Create DateTime Variable

and Insert following script

@[User::DATALOADDIR]+"S30_SERINDEX_" +
Right("0" + (DT_STR,4,1252) DatePart("DD",DATEADD("DD",-1,getdate()  ) ),2)
 +
Right("0" + (DT_STR,4,1252) DatePart("MM",DATEADD("DD",-1,getdate()  ) ),2)
+Right("0" + (DT_STR,4,1252) DatePart("YY",DATEADD("DD",-1,getdate()  ) ),4)

+".CSV"
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

776 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