Get records from current year

[21KristianN]

Dear SQL sharks....

Please somebody help me with this struggle. I have been searching the web for hours now.

I have a table with sales data. In this table i have a columne called SalesAmount, CustomerName and SalesDate(the format i smalldatetime)

Now I would like to retreive all the records where SalesDate is within current year...How do I do this??
I want to make a sum of this years sales numbers and when i pass newsyear it should start from zero again.

I really hope somebody can help me!!

Kristian

[cgyrob]

There are alot of ways to do this here are a couple examples.

Using Year() function

Select sum(Salesamount)
From Table
where Year(salesdate) = Year(@userInput)

using Date functions

Select sum(Salesamount)
From Table
where salesdate between DATEADD(yy,DATEDIFF(yy,0,@userInput),0) --'First Day of Year' 
and DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@userInput)+1,0))) --'Last Day of Year'

[21KristianN]

Thanks for super fast reply - but is it possible to do this without userinput? So the query is based on the current year of the servertime?

[cgyrob]

Just replace where I had @userInput with getdate() function

SELECT sum(Salesamount)FROM TableWHERE YEAR(salesdate) = YEAR(getdate())

You can do the same with the date functions example.

[sknake]

The only downside to doing it with Year() on the search column is indexes wont be used since you're running a function on the column you want to search on. Ideally you should use date ranges wherever possible.

But cgyrob was awesome and gave you the solution using between() so you're all set :)

[kplcjl]

What sknake said, however he didn't give an example. You need to provide constants in the where clause, so calculate constants. If you are only recording current datetimes in the table you only need to find "WHERE SalesDate >= @dt1". If you are recording future dates as well or if you want to go to some year in the past use "WHERE SalesDate BETWEEN @dt1 AND @dt2".

DECLARE @dt1 SMALLDATETIME, @dt2 SMALLDATETIME, @dtyr CHAR(4)
SELECT @dtyr=YEAR(GETDATE()),@dt1=@dtyr + '0101',@dt2=DATEADD(YEAR,1,@dt1),@dt2=DATEADD(MINUTE,-1,@dt2)

[kplcjl]

Sorry, I didn't explain what I did.
"@dtyr=YEAR(GETDATE())" automatically casts the INT result of the YEAR() function into a 4 character string. The string format "YYYYMMDD" is globally recognized as a valid date format, no matter what format your local date setting uses. (IE "DD/MM/YYYY", or "MM/DD/YYYY", etc.) so "@dt1=@dtyr + '0101'" is the first day of the first month of this year at midnight. "DATEADD(YEAR,1,@dt1)" should explain itself, however if you use that in the BETWEEN statement it would include next year's data that started at January 1 midnight so "@dt2=DATEADD(MINUTE,-1,@dt2)" excludes next year's data. If you use DATETIME instead you would need to subtract 3 milliseconds. (If you did that and your comparison field was smalldatetime it would still round up to next year in the comparison.)

PS You should subscribe to SQLServerCentral, you get interesting articles e-mailed to you periodically. They just had an extensive article about this very issue.

[cgyrob]

Why are you creating constants when you can just use the date functions in the where clause?

SELECT sum(Salesamount)
   FROM Table
   WHERE salesdate BETWEEN   DATEADD(yy,DATEDIFF(yy,0,getdate()),0) --'First Day of Year'
   AND DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))) --'Last Day of Year'

[21KristianN]

Thanks.... that was exactly what i needed... Perfect!!! Thank you for helping me.

[kplcjl]

Why are you creating constants when you can just use the date functions in the where clause?

SELECT sum(Salesamount)
   FROM Table
   WHERE salesdate BETWEEN   DATEADD(yy,DATEDIFF(yy,0,getdate()),0) --'First Day of Year'
   AND DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))) --'Last Day of Year'

The key phrase is "functions in the where clause" As someone else has stated in this thread, this is a bad idea, expecially when selecting from indexed fields. First you are causing SQL to execute a process for every single row in the table. This increases CPU processing which slows down response. It does this for every row because functions are designed to give dynamic results and SQL doesn't realized this is producing a constant. By my count you are executing 6 functions. In a table with 20 million rows in it, that's 120 million executions and should take minutes if not hours to process.
That's if you are looking for 1 year or 1 hour, indexed or not indexed.
NEVER, EVER, EVER put functions that produce a constant value in SQL where clauses. (Unless you happen to like poor response and poor performance.)
PS I think my query is easier to read than yours with separate statements, 3 functions, sequentially executed.
PPS Whoops, miscounted ... 8 functions in yours, 3 in mine because I also removed the Midnight date error your query still has.

[cgyrob]

Thanks for the response.

The cost on using functions on a where clause come when using the function on the column because it causes a table scan even on an indexed field. When the function is used on the opposite side of the column it has no effect on the index seek and limited to no query cost.

[kplcjl]

This might be true on SQL 2005. I compared execution plans and the cost was 50/50 using functions and constants. There was no discernable difference in retrieval speeds, but I wasn't using a large table to do so.
What I do know is a user had defined a hash function and was using it on the other side from the column. It was taking 13 seconds to retrieve the data that matched the hash. Changed the routine to use a constant varchar variable and the response time went to 25 milliseconds. I also know I read a book that says the function is executed against every row in a table and shouldn't be used when the result is a constant value.
Of course the book and I can be out of date now.

[Tarang Raval]

i am search in table admin columns
admin columns current year display in the system
admin columns datatype is DATE/TIME

please give me sql query

[kplcjl]

The point isn't to avoid using functions at all, just don't use them in the query.

DECLARE @dt1 datetime = CAST(DATEPART(YEAR,GETDATE()) AS CHAR(4))+'0101'
DECLARE @dt2 datetime = DATEADD(YEAR,1,@dt1)
-- You now have a range of dates for the current year in variables that remain constant in the query.
...WHERE DateOfInterest BETWEEN @dt1 AND @dt2