CODEWITHSUNDEEP
sqlsql-serverdate-formatting
WWaldo
WWaldo

Reputation: 223

Yesterday into YYYYMMDD format in SQL Server

I'm looking to add in a condition to my already badly performing SQL code. I want to add in a filter where the date = yesterday (data type as INT).

For example 

Select * 
From table
Where Date = 20190930

How do I do this using GETDATE() - 1?

Upvotes: 0

Views: 2562

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13016

Here's your query.

Firstly, you need to cast your int date to a varchar before covenrting to datetime, to avoid an arithmetic flow error during conversion.

Secondly, you need to cast getdate() - 1 as date to truncate time to match your date field.

select *
from table
where cast((cast(date as varchar(8))as datetime) = cast(getdate() - 1 as date)

or 

select *
from table
where cast((cast(date as varchar(8)) as date) = cast(dateadd(day,datediff(day,1,GETDATE()),0) as date)

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416081

We need to be very clear about what data type you're using.

If you have a simple date value (no time component as part of the data type), things are pretty easy:

Select * 
from table
where Date = DATEADD(day, -1, cast(current_timestamp as date))

If you have a DateTime or DateTime2 value, it's important to understand that all DateTime values have a time component that goes all the way down to milliseconds. This is true even when you expect the time component to always be at or near midnight. That can make straight equality comparisons difficult. Instead, you almost always need check within a specific range:

Select * 
from table
where Date >= DATEADD(day, -1, cast(current_timestamp as date))
    AND Date < cast(current_timestamp as date)

Upvotes: 3

Related Questions