data between 1st jan to December 31st of previous year query --no hard coded dates.

sai 2012

Hi all, If I want to run the query today(12/2/2017) then my data should be inbetween 2016 January 1st to December 31st --I don't want to hard code dates. if I want to run the query on jan 1st 2018 or in any day in 2018 then my data should be inbetween 2017 January 1st to 2017 December 31st. if I want to run the query in 2019 then my data should be inbetween 2018 January 1st to 2018 December 31st. how to write query, please advice.

Victor Nijegorodov

1. Obtain current date -> obtain "current year" -> obtain ("current year" - 1) 2. Make the dates for "between": [("current year" - 1)-01-01] and [("current year" - 1)-12-31] 3. use them in your query.

Chris Quinn

WHERE YEAR([date_column]) = YEAR(DATEADD(yy,-1,getdate()))

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

jschell

Your requirements are bad. (I would presume and hope that they were made up by a teacher.) The second two requirements can be expressed as Req: Given today's date run a query that spans the 'prior' year based on that date. The first requirement is incompatible with that and it is impossible to implement without other input data. One way to do that is in fact to hard code 2017 as a switch value. The second way is to presume that the target table has no data before 2017. However to implement that latter rule would mean that EVERY query would need to do a second query to determine that no data existed. Not to mention that for users it would be confusing at least over time.

Richard Deeming

You have already posted this in QA: Data between jan 1st yyyy(always previous year) to december 31st yyyy(always previous year) query in where clause[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer