Solved

SELECT * where TODAY minus ONE DAY

Posted on 2007-03-28
8
19,510 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
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.

 
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 …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

919 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

13 Experts available now in Live!

Get 1:1 Help Now