How can I pick mid month?

Question

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

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

Can you explain the business rules a bit more? ie: what has to happen if the current date is 04/20/05 ??

--------------------
Colt 45 - the original point and click interface

joemai Hall of Fame Points: 3496 More actions · Answer 2

We always process the payment on 15 or end of the month. Therefore, my data always stable and there is always no exception. Thanks

Minh Vu

philcart SSC-Forever Points: 47796 More actions · Answer 3

That's still pretty vague.

Is this date stored somewhere and you want to change it after you've performed the processing??

--------------------
Colt 45 - the original point and click interface

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 4

my 2ct (not knowing the exact buisiness rrule)

select case when day(getdate()) > 14 then

DATEADD(d, -1, DATEADD(m, 1 + DATEDIFF(m, 0, getdate()), 0))

else

convert(datetime,cast(year(getdate()) as char(4)) + '-' + cast(month(dateadd(m,1,getdate())) as char(2)) + '-' + '15', 121)

end as paymentdate

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Piggy-backing on alzdba, what about this?

SELECT

CASE

WHEN DAY(GETDATE()) > 14

THEN DATEADD(mm,1,DATEADD(DAY,1-DAY(GETDATE()),DATEDIFF(d,0,GETDATE())))-1

ELSE DATEADD(mm,1,DATEADD(DAY,15-DAY(GETDATE()),DATEDIFF(d,0,GETDATE())))

END

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

joemai Hall of Fame Points: 3496 More actions · Answer 6

Thanks a lot Frank.

That is exactly what I need.

Minh vu