Solved

SELECT * where TODAY minus ONE DAY

Posted on 2007-03-28
8
19,247 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now