SQLTeam.com | Weblogs | Forums

SQL date conversion minus days


#1

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.


Subtract one day from getdate()
#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()


#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!!!


#4

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)


#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.


#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


#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

#8

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.