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

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

Using Year() function

using Date functions

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?

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


You can do the same with the date functions example.

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

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

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.

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

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

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.