how to change month beg and end dates

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to change month beg and end dates

Author

Topic

arnae92
Starting Member

5 Posts

Posted - 2011-11-23 : 14:52:21

I'm working with ssrs expressions. My dataset has dates starting from 12/19/10 through 11/20/2011. I need to create a report that will group on the calendar month first then group on the our company's fiscal month, which starts on the 19th of each month and ends on the following month on the 18th. All I've been able to do is group on months and dates of the month on regular calendar dates. Help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-23 : 15:04:54

If you want to group by calendar month, use this:


GROUP BY
   DATEDIFF(mm,'19000101'YourDateColumn);

If you want to group by dates from 19th of a month to 18th of the following month, use this:


GROUP BY
   DATEDIFF(mm,'19000101',DATEADD(mm,CASE WHEN DAY(YourDateColumn) <= 18 THEN -1 ELSE 0 END, YourDateColumn))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources