Computing First paydate of quarter

Question

Post reply

Computing First paydate of quarter

bgsxygrmn

SSC Enthusiast

Points: 139
More actions
January 25, 2010 at 1:20 pm

#219853

Can anyone help me compute the first paydate of the current quarter? This is assuming that there's 14 day intervals for paydates and 1/1/2010 is a paydate.
So say the current date is july 27th 2010. The first paydate of the quarter would be july 2nd 2010.
Can anyone help?
Thanks

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Go here I am sure you can find what you need.

https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

bgsxygrmn SSC Enthusiast Points: 139 More actions · Answer 2

Thanks, but that doesn't really help me at all. I think that I would have to combine two of the functions that are listed in the link.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

bgsxygrmn
Thanks, but that doesn't really help me at all. I think that I would have to combine two of the functions that are listed in the link.

Do not understand your:

that doesn't really help me at all.

You want someone to give you the complete T-SQL statement?

Remember People here help those who help themselves

Please read the first link in my signature block for the correct procedure to post a question, paying particular attention to table definition, sample data, expected results and what you have done

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

declare @FirstPayDate datetime

set @FirstPayDate= '20100101'

select

*,

FirstPayDateOfCurrentQuarter =

dateadd(dd,(datediff(dd,@FirstPayDate,dateadd(dd,13,

dateadd(qq,datediff(qq,@FirstPayDate,Date),@FirstPayDate)))/14)*14,@FirstPayDate)

from

(

select Date = convert(datetime,'20100727')union all

select Date = convert(datetime,'20100827')union all

select Date = convert(datetime,'20101001')

) a

order by

a.Date

Results:

Date FirstPayDateOfCurrentQuarter

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

2010-07-27 00:00:00.000 2010-07-02 00:00:00.000

2010-08-27 00:00:00.000 2010-07-02 00:00:00.000

2010-10-01 00:00:00.000 2010-10-08 00:00:00.000

(3 row(s) affected)

bgsxygrmn SSC Enthusiast Points: 139 More actions · Answer 5

Thanks Michael,

That did the trick. I edited your code to:

select dateadd(dd,(datediff(dd,'1/1/2010',dateadd(dd,13,

dateadd(qq,datediff(qq,'1/1/2010',getdate()),'1/1/2010')))/14)*14,'1/1/2010')

and that worked perfectly. I don't think that I would have figured it out just using one dateadd and one datediff

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 6

option: add it to a calendar table, logging which days are paydates.

Similar stuff: http://jasonbrimhall.info/?p=193

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."