Hi All,

I am building few reports in which each quarter data needs to be shown separately. I am using  DATENAME ( datepart ,date )  as part of my SQL. However, it seems that quarters are calculated for year starting 01 January. I need to set it up so the year starts at 01 July instead.

Can anyone suggest a work around this?

Hemal

HI Chris,

Thank you for your suggestion.

I figured out a work around i.e. DATENAME(qq, dateadd(qq, -2, datecreate)

I am not sure this is the best way to do it.

Thanks,

Hemal

Hi Hemal

I might be missing something, but DATENAME(datepart, date) where datepart = 'q' returns a nvarchar value '1' through '4'. Couldn't you just accept '1' as 3rd financial quarter, '2' as 4th financial quarter, '3' as 1st financial quarter and '4' as 2nd financial quarter?

If the actual value needs to be char representing the financial quarter then:

---

From: SQLDownUnderList@... [mailto:SQLDownUnderList@...] On Behalf Of Hemal Modi
Sent: Monday, 14 July 2008 4:57 PM
To: SQLDownUnder@...
Subject: [SQLDownUnder] SSRS: Set the start of Financial Year

Hi All,

 

I am building few reports in which each quarter data needs to be shown separately. I am using  DATENAME ( datepart ,date )  as part of my SQL. However, it seems that quarters are calculated for year starting 01 January. I need to set it up so the year starts at 01 July instead.

 

Can anyone suggest a work around this?

 

Hemal

rzzuyrynzoirjyv

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

If you will be doing many date calculations, particularly ones that involve public holidays or business specific periods, its well worth creating a calendar table.

A calendar table lists every single date as a unique key, then lists a whole string of attributes against that date, i.e. what quarter the date lies in, what year, whether it’s a public holiday etc.

You need to pre populate the table but it is very helpful for reporting purposes. It is often much clearer to join to this table (or make it available to an end user) than use an enormous inline date function.

Nick

---

From: SQLDownUnderList@... [mailto:SQLDownUnderList@...] On Behalf Of Hemal Modi
Sent: Monday, 14 July 2008 5:39 PM
To: SQLDownUnder@...
Subject: RE: [SQLDownUnder] SSRS: Set the start of Financial Year

HI Chris,

 

Thank you for your suggestion.

 

I figured out a work around i.e. DATENAME(qq, dateadd(qq, -2, datecreate)

 

I am not sure this is the best way to do it.

 

Thanks,

 

Hemal

 

From: SQLDownUnderList@... [mailto:SQLDownUnderList@...] On Behalf Of Fredericks, Chris
Sent: Monday, 14 July 2008 3:28 PM
To: SQLDownUnder@...
Subject: RE: [SQLDownUnder] SSRS: Set the start of Financial Year

 

Hi Hemal

 

I might be missing something, but DATENAME(datepart, date) where datepart = 'q' returns a nvarchar value '1' through '4'. Couldn't you just accept '1' as 3rd financial quarter, '2' as 4th financial quarter, '3' as 1st financial quarter and '4' as 2nd financial quarter?

 

If the actual value needs to be char representing the financial quarter then:

Select Case DATENAME( qq, <date_value> )
         When '1'
           Then '3'
         When '2'
           Then '4'
         When '3'
           Then '1'
         When '4'
           Then '2'
        End

Cheers
Chris

---

From: SQLDownUnderList@... [mailto:SQLDownUnderList@...] On Behalf Of Hemal Modi
Sent: Monday, 14 July 2008 4:57 PM
To: SQLDownUnder@...
Subject: [SQLDownUnder] SSRS: Set the start of Financial Year

Hi All,

 

I am building few reports in which each quarter data needs to be shown separately. I am using  DATENAME ( datepart ,date )  as part of my SQL. However, it seems that quarters are calculated for year starting 01 January. I need to set it up so the year starts at 01 July instead.

 

Can anyone suggest a work around this?

 

Hemal

rzzuyrynzoirjyv

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

How about :

select ((datepart(q, <date_value>)+ 1)%4)+1

testing:

declare @datecreate as datetime

set @datecreate = '2008-01-01'

select @datecreate dateCreate , ((datepart(q,@datecreate)+ 1)%4)+1 FinQrt

set @datecreate = '2008-04-01'

select @datecreate dateCreate , ((datepart(q,@datecreate)+ 1)%4)+1 FinQrt

set @datecreate = '2008-07-01'

select @datecreate dateCreate , ((datepart(q,@datecreate)+ 1)%4)+1 FinQrt

set @datecreate = '2008-12-01'

select @datecreate dateCreate , ((datepart(q,@datecreate)+ 1)%4)+1 FinQrt

---

From: SQLDownUnderList@... [mailto:SQLDownUnderList@...] On Behalf Of Hemal Modi
Sent: Monday, 14 July 2008 5:39 PM
To: SQLDownUnder@...
Subject: RE: [SQLDownUnder] SSRS: Set the start of Financial Year

HI Chris,

 

Thank you for your suggestion.

 

I figured out a work around i.e. DATENAME(qq, dateadd(qq, -2, datecreate)

 

I am not sure this is the best way to do it.

 

Thanks,

 

Hemal

 

From: SQLDownUnderList@... [mailto:SQLDownUnderList@...] On Behalf Of Fredericks, Chris
Sent: Monday, 14 July 2008 3:28 PM
To: SQLDownUnder@...
Subject: RE: [SQLDownUnder] SSRS: Set the start of Financial Year

 

Hi Hemal

 

I might be missing something, but DATENAME(datepart, date) where datepart = 'q' returns a nvarchar value '1' through '4'. Couldn't you just accept '1' as 3rd financial quarter, '2' as 4th financial quarter, '3' as 1st financial quarter and '4' as 2nd financial quarter?

 

If the actual value needs to be char representing the financial quarter then:

Select Case DATENAME( qq, <date_value> )
         When '1'
           Then '3'
         When '2'
           Then '4'
         When '3'
           Then '1'
         When '4'
           Then '2'
        End

Cheers
Chris

---

From: SQLDownUnderList@... [mailto:SQLDownUnderList@...] On Behalf Of Hemal Modi
Sent: Monday, 14 July 2008 4:57 PM
To: SQLDownUnder@...
Subject: [SQLDownUnder] SSRS: Set the start of Financial Year

Hi All,

 

I am building few reports in which each quarter data needs to be shown separately. I am using  DATENAME ( datepart ,date )  as part of my SQL. However, it seems that quarters are calculated for year starting 01 January. I need to set it up so the year starts at 01 July instead.

 

Can anyone suggest a work around this?

 

Hemal

rzzuyrynzoirjyv

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net