[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SELECT * where TODAY minus ONE DAY

Posted on 2007-03-28
8
Medium Priority
?
24,270 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
[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
8 Comments
 
LVL 11

Accepted Solution

by:
Ved Prakash Agrawal earned 800 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 400 total points
ID: 18808824
SELECT * from Orders
where datediff(day,OrderDate,GETDATE()) = 1
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 400 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Assisted Solution

by:Ved Prakash Agrawal
Ved Prakash Agrawal earned 800 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 400 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

649 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