I couldn't find my original post, I apologize. I am using Select Convert(varchar(17), GETDATE(),110) to get the current date which works fine. But, I actually need yesterday, so per another forum, I put -1 at the end and I get the error "Conversion failed when converting the varchar value '10-21-2016' to data type int.". Other forums have lots of advice but I can't seem to get it. Any help would be marvelous.

# SQL date conversion minus days

**JamesK**#2

Do the subtraction on the GETDATE().

So one possibility (which I don't recommend) is this:

`CONVERT(varchar(17), GETDATE()-1,110)`

This will work, but it is depending on an implementation detail. So even though the following looks a little bit more wordy, you should use the following:

`CONVERT(varchar(17), DATEADD(day,-1,GETDATE()),110)`

The expression DATEADD(day,-1,GETDATE()) is subtracting 1 day from the value returned by GETDATE()

**nelsonr3**#3

The latter option worked perfectly and I was able to use it in a Where clause to properly select all of the previous day's data. Thank you so much!!!

For formatting of output, the CONVERT is fine.

**But don't use CONVERT against any table column in WHERE clauses!** It can really hurt performance.

Instead do this to get yesterday's data:

WHERE column_name >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) AND

column_name < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

**Kristen**#5

Just to point out, in case important, that this is "subtracting 1 day" from the EXACT TIME now, so this is NOT Midnight-yesterday. So if you use it as a range you will start from "this exact time yesterday" rather than "all of yesterday".

@ScottPletcher code

```
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)
```

will "round" the GETDATE()) - 1 value to "whole days" so will be start-of-day, yesterday.,

It looks like a complicated formula but the benefit (over converting to VARCHAR) is that it is all integer arithmetic, so is much more efficient that a string conversion.

Also, SQL is notoriously ambiguous in Date/String conversions - it depends on the Server Settings, and even the Language of the currently connected user, so is prone to change and likely to cause programs to fail unexpectedly.

**nelsonr3**#6

Ok…so if I’m looking for the past 12 months…my formula should look like

Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -12, 0) AND

Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Is that correct? Unfortunately, I am unable to ascertain what data may or may not be within that timeframe so I’m just trying to get the formula(s) correct so I can take it to the power-user and let them hash out why there’s no data.

Thanks,

Ray Nelson

Integration Business/Programmer Analyst II

Albany Medical Center

(518)264-1985

**Kristen**#7

Looks OK to me. This would enable you to test the parameters to see if they are exactly the cuttoff points that you want:

```
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -12, 0) AS StartDate
, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS EndDate
```

Exactly ... nicely done! You can fine tune the date calc if/as needed to get the exact date range you need, but the core technique is what's really important.