Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ALSZ37
Starting Member
25 Posts |
 Posted - 2015-02-23 : 10:24:53
|
| Good Morning,I am running a count, but I only want it to count records based on the current calendar year. I searched online and see that maybe I could use year (getdate()), but not sure how to use it. Is it supposed to be in the where clause?Select TX_Location,TX_Date,part,count (part) 'Countfrom view_Dataset1where (part=1 or part =2)group by tx location, part |
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2015-02-23 : 10:40:30
|
| If it's a one time report you could hard code the date values in:WHERE (part=1 or part =2) AND TX_Date >= '20150101' and TX_DATE < '20160101'But if you want to use this for next year, and the year after etc, (which is the better way) then you want to do some calculations to get the first of the current year through the end of the year, or the current day (depends on business logic)WHERE (part=1 or part =2) AND TX_Date >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) and TX_DATE < DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0)DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) gives you the first day of the current year (Jan 1 of whatever year you run it)DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0) gives you the first day of the next year (Jan 1 of whatever year you run it).By putting a >= first, and a < second, it will catch all records greater than or equal to Jan 1st at 00:00 and less than or equal to Dec 31 at 23:59 |
 |
|
|
ALSZ37
Starting Member
25 Posts |
Posted - 2015-02-23 : 11:44:05
|
quote:
Originally posted by ITTrucker
If it's a one time report you could hard code the date values in:WHERE (part=1 or part =2) AND TX_Date >= '20150101' and TX_DATE < '20160101'But if you want to use this for next year, and the year after etc, (which is the better way) then you want to do some calculations to get the first of the current year through the end of the year, or the current day (depends on business logic)WHERE (part=1 or part =2) AND TX_Date >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) and TX_DATE < DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0)DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) gives you the first day of the current year (Jan 1 of whatever year you run it)DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0) gives you the first day of the next year (Jan 1 of whatever year you run it).By putting a >= first, and a < second, it will catch all records greater than or equal to Jan 1st at 00:00 and less than or equal to Dec 31 at 23:59
Thank you! I will use the bottom code since this will be a report and I don't want to have to change it every year. On another note is there a way for the data to return a count of zero if there is none? Right now if I run it for those that do not have any tx_date for this calendar year it does not bring back anything. I still want to show the tx_location and part. |
|
|
|
naughtyca
Starting Member
1 Post |
Posted - 2015-03-12 : 10:43:53
|
| Hello I think my issue trying to get current year data is almost the same as this but im getting error The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.SELECT DISTINCT mFROM table.dbo.mWHERE (m >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) AND (m < DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0))The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.thanks |
|
|
|
|
|
|
|
|
Current Calendar Year