Get current year in TSQL

问题:

I have data I am trying to pull for a report and I am working on a Year to Date report. My columns in the table are formatted as datetime. I am trying to run a select statement to get all the results where date = this year.

For example:

SELECT  A.[id], A.[classXML]
FROM   tuitionSubmissions as A
WHERE A.[status] = 'Approved' AND A.[reimbursementDate] = THISYEAR
FOR    XML PATH ('data'), TYPE, ELEMENTS, ROOT ('root');

Is there an eay way to acomplish this?

回答1:

Yes, it's remarkably easy in fact:

WHERE YEAR(A.[reimbursementDate]) = YEAR(GETDATE())

回答2:

This should be a better way if performance is an issue (although it isn't as readable)

where A.[reimbursementDate] between  
     DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) and
     DATEADD(MILLISECOND, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))

Those funky DATEADD statements return the first and last days of the current year (through December 31 23:59:59.997). Since reimbursementDate isn't contained in a function, the query will be able to take advantage of any applicable indexes.