Sql Server Date object

[system]

Hi All,
I need to show rolling last 15 months data, so that whenever the report is refreshed it shows the previos 15 months.

The DB is sql server 2008. So I Need the syntax for a filter object which will show Data between last month and previous 15 months.
Appreciate the help!

---

americanmc (BOB member since 2009-12-31)

[system]

Hi,

This sticky topic might help, sort of:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks…however I couldn’t find something to guide for rolling last 15 months.
Secondly this particular syntax is not parsing for me in IDT.
Need help!

---

americanmc (BOB member since 2009-12-31)

[MarkP]

@americanmc

Thanks…however I couldn’t find something to guide for rolling last 15 months.
Secondly this particular syntax is not parsing for me in IDT.
Need help!

OK, first thing to make clear - these relative date objects will not parse in Designer. The Designer parsing algorithm expects a table present. As relative date objects use the getdate() system function they will not parse in Designer. However, because you want to use them to return transactional data, they will work because you’re comparing your transaction data to “15 months ago”.

Now that’s done with, let’s be more precise with your requirement.
Today is 16/7/13. You say rolling 15 months. But do you want:
16/4/12 - 15/7/13 (exact 15 months)
1/4/12 - 30/6/13 (last 15 full months)
1/5/12 - 15/7/13 (last 15 months including this month)

Whichever you choose, the sticky topic already has the end date construct sorted out for you. But which is it?

[system]

Thanks Mark,
I need the 1/4/12 - 30/6/13 (last 15 full months)

Appreciate!

---

americanmc (BOB member since 2009-12-31)

[MarkP]

OK, so you can get End of Previous Month from the sticky

Use the Start of Current Month object from the sticky as below:
dateadd(MM,-15,)

[system]

I might add a little here. The sticky is great and gives great syntax. Definitely check it out.

The code DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) gives you this month’s first day of the month. DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-15), 0) gives you the first day of the month, 15 months prior.

If you have a date dimension, I have previously put a view on top of the date dimension with the specific SQL to get the fields that way I’m able to reuse the logic elsewhere aside from just in the universe. In this case a “Months from today” type column would prove invaluable for all of your report developers and would be very easy for everyone to use in a query.

---

metricfoundry (BOB member since 2013-07-16)

[MarkP]

@metricfoundry

I might add a little here. The sticky is great and gives great syntax. Definitely check it out.

The code DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) gives you this month’s first day of the month. DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-15), 0) gives you the first day of the month, 15 months prior.

If you have a date dimension, I have previously put a view on top of the date dimension with the specific SQL to get the fields that way I’m able to reuse the logic elsewhere aside from just in the universe. In this case a “Months from today” type column would prove invaluable for all of your report developers and would be very easy for everyone to use in a query.

Totally agree. The one sticky that we probably need in here that we haven’t got is on the Calendar Table, what columns to include and how to build/repopulate it.