# Simple SQL Date Question

• ### Question

• Hi Guys

OK, so pretty basic with this but hope this makes sence;

I have a few columns (each row is by day). "Persons Name" "Hours in Day" "Cost for Day" & "Date"

So this is like a simple employees report.

Can I, write something which uses the date, and gives me a value Year To Date? (YTD)

Sunday, April 7, 2013 2:42 PM

• ```SELECT SUM(CostForDay) FROM myTable
WHERE YEAR([DATE]) = YEAR(GETDATE())```

• Proposed as answer by Sunday, April 7, 2013 2:50 PM
• Unproposed as answer by Sunday, April 7, 2013 2:53 PM
• Proposed as answer by Monday, April 8, 2013 1:22 PM
• Unproposed as answer by Monday, April 8, 2013 1:22 PM
• Proposed as answer by Monday, April 8, 2013 1:24 PM
• Marked as answer by Monday, April 15, 2013 7:58 AM
Sunday, April 7, 2013 2:48 PM
• May be

```SELECT [Persons Name], SUM([Hours in Day]) as [Total Hours], SUM([Cost for Day]) as [Total Cost]

FROM EmployeeHours

WHERE [Date] >=dateadd(year, datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101') -- beginning of the year

AND [Date] < dateadd(year, 1+datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101') -- start of next year

GROUP BY [Persons Name]```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Sunday, April 7, 2013 2:53 PM

### All replies

• ```SELECT SUM(CostForDay) FROM myTable
WHERE YEAR([DATE]) = YEAR(GETDATE())```

• Proposed as answer by Sunday, April 7, 2013 2:50 PM
• Unproposed as answer by Sunday, April 7, 2013 2:53 PM
• Proposed as answer by Monday, April 8, 2013 1:22 PM
• Unproposed as answer by Monday, April 8, 2013 1:22 PM
• Proposed as answer by Monday, April 8, 2013 1:24 PM
• Marked as answer by Monday, April 15, 2013 7:58 AM
Sunday, April 7, 2013 2:48 PM
• I'm not sure exactly what you want.  For this type of question it is helpful if you give us some sample data, the result you would want from that sample data and the release of SQL Server you are using.

Tom

Sunday, April 7, 2013 2:50 PM
• May be

```SELECT [Persons Name], SUM([Hours in Day]) as [Total Hours], SUM([Cost for Day]) as [Total Cost]

FROM EmployeeHours

WHERE [Date] >=dateadd(year, datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101') -- beginning of the year

AND [Date] < dateadd(year, 1+datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101') -- start of next year

GROUP BY [Persons Name]```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Sunday, April 7, 2013 2:53 PM
• Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. If you have to work from specs this vague, update your resume.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Sunday, April 7, 2013 4:50 PM
• You unproposed an answer that was effectively the same as yours - and both are incorrect.  YTD generally means Jan 1 to the current date.  Without further knowledge of the data to be queried, it would be inappropriate to suggest a query that includes everything with a date that falls in the current year.  There are many valid reasons to post a transaction that falls in the future - even if only a few days in the future (we do it all the time).
Monday, April 8, 2013 1:06 PM
• The reason I unproposed that answer was because it would have been non-sargable and bad performing. But I decided that perhaps the idea was OK and that's what the OP wanted. Without more input from OP we can only guess.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Monday, April 8, 2013 1:09 PM
• But I guess your other point is valid - we wanted the upper limit to be current date, not the beginning of the next year. It is easy to fix the query I posted by stripping the date portion of the current date.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Monday, April 8, 2013 1:11 PM