CODEWITHSUNDEEP
sqlsql-serverdate
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Returning results with yesterdays date

I have a join that returns results for today but i want it to return yesterdays values.

FROM
Person
JOIN Profit 
ON Person.id = Profit.id 
AND [DATE] = CAST(getdate () -1  as Date)

^^ This returns the current date.

I have then tried to return yesterdays date:

 [DATE] = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1)

 [DATE] = DATEADD(day,DATEDIFF(day,0,GETDATE())-1,0)

 [DATE] = dateadd(DD, -1, cast(getdate() as date))

But none of these work. Can anyone help? thanks

Upvotes: 0

Views: 127

Answers (6)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I think you should use Between as below : 

FROM
Person
JOIN Profit 
ON Person.id = Profit.id 
AND [DATE] BETWEEN CONVERT(date, GETDATE()-1) AND CONVERT(date, GETDATE()-1)

Upvotes: 0

Laravel BAP
Laravel BAP

Reputation: 1

if You have column with date:

SELECT * from Person p join Profit pr 
on (p.id = pr.person_id and pr.`date` = SUBDATE(CURDATE(),1))

Upvotes: 0

Amit Kumar Singh
Amit Kumar Singh

Reputation: 4475

Use this if you do not want time part of date. 

SELECT * 
FROM Person 
    JOIN Profit ON Person.id = Profit.id 
   AND [DATE] = CONVERT(VARCHAR, DATEADD(DAY, -1, GETDATE()), 106)

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10711

If you perform 

select DATEADD(day, -1, cast(SYSDATETIME() as DATE))
select dateadd(DD, -1, cast(getdate() as date))

then yesterday date is returned. Therefore, the only problem can be in the format of your [DATE] attribute.

Upvotes: 1

PeterH
PeterH

Reputation: 1040

it may be that [DATE] also needs converting :

FROM
    Person
JOIN 
    Profit 
ON 
    Person.id = Profit.id 
AND CAST([DATE] as Date) = CAST(getdate () -1  as Date)

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

Simply:

FROM
Person
JOIN Profit 
ON Person.id = Profit.id 
WHERE Table.[DATE] = DATEADD(DAY, -1, GETDATE() );

Upvotes: 0

Related Questions