Solved

SELECT * where TODAY minus ONE DAY

Posted on 2007-03-28
8
20,944 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why is this SQL bringing back extra rows? (parsing XML data) 4 39
Regarding Disk IO 3 49
SQL Syntax 6 41
Merge join vs exist 3 27
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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