Function to get "day of Quarter" in T-SQL or DTS ?

Question

Post reply

Function to get "day of Quarter" in T-SQL or DTS ?

Raj Malwade

Mr or Mrs. 500

Points: 594
More actions
July 15, 2004 at 3:26 pm

#108501

Hi :
I am trying to retrive the number of day in a quarter based on date using T-SQL or DTS. For example: today is 07/15/2004. I would like to retrive in "day_of_calendar_quarter" value 15. Since I am on 15th day of Quarter 3.
If today is 08/15/2004, the I would like to retrive value 46, since 08/15/2004 is the 46th day of Quarter 3.
I am trying to use DATPART function. I can get "day of week", "day of month" and "day of year" by using DATEPART(w,getdate()), DATEPART(d,getdate()), and DATEPART(dy,getdate()) respectively. HOw do I get the similar day for quarter ?
Thanks for your help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rockmoose SSCarpal Tunnel Points: 4571 More actions · Answer 1

declare @myday datetime

set @myday = '07/15/2004'

select datediff(day,dateadd(quarter,datediff(quarter,0,@myday),0),@myday) + 1 as day_of_quarter

day_of_quarter

--------------

15

set @myday = '08/15/2004'

select datediff(day,dateadd(quarter,datediff(quarter,0,@myday),0),@myday) + 1 as day_of_quarter

day_of_quarter

--------------

46

/rockmoose

You must unlearn what You have learnt

JourneymanProgrammer SSCommitted Points: 1546 More actions · Answer 2

hey,

i think the answer is:

CREATE FUNCTION dbo.fn_day_of_quarter(@dayInQuarter AS DATETIME) RETURNS INTEGER

AS

BEGIN

DECLARE @month AS INTEGER

SET @month = DATEPART(m, @dayInQuarter)

DECLARE @firstDayInQuarter AS DATETIME

SET @firstDayInQuarter = CAST( CAST( 1 + 3 * FLOOR((@month - 1) / 3.0) AS VARCHAR ) + '/1/' + CAST( YEAR(@dayInQuarter) AS VARCHAR ) AS DATETIME )

RETURN ( DATEDIFF(d, @firstDayInQuarter, @dayInQuarter) + 1)

END

You would call the function like this:

dbo.fn_day_of_quarter( GETDATE() )

hope this helps ...

Raj Malwade Mr or Mrs. 500 Points: 594 More actions · Answer 3

Hello:

Thanks for your help. This certainly works in "Execute SQL Task" in DTS

Both the answers work. But how do I do this in Active X script of Transform Data Task ?

Thanks for your help.

rockmoose SSCarpal Tunnel Points: 4571 More actions · Answer 4

Why do You have to do it as an Active X script ?

You could specifiy a Query with apropriate SQL as the source.

/rockmoose

You must unlearn what You have learnt

Peter P SSCrazy Points: 2777 More actions · Answer 5

If it does turn out you need to do it in ActiveX script, here you go:

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

x = DayOfQuater(now())

msgbox x

Main = DTSTaskExecResult_Success

End Function

Public Function DayOfQuater(dtDayInQuarter)

Dim iMonth

iMonth = Month(dtDayInQuarter)

Dim iFirstDayInQuarter

iFirstDayInQuarter = CDate(CStr(1 + 3 * Abs(iMonth - 1) / 3) & "/1/" & CStr(Year(dtDayInQuarter)))

DayOfQuater = DateDiff("d", iFirstDayInQuarter, dtDayInQuarter)

End Function

Raj Malwade Mr or Mrs. 500 Points: 594 More actions · Answer 6

Thank you very much for everybody's help. It works.