SQL: Finding 2nd Tuesday in a month using sysdate

Lead_Head · 17 Jun 2009 at 14:28

Hello all,

Does anyone know how to find the second Tuesday of a month, every month using sysdate?

I'm trying to schedule a script which will check sysdate and then work out from sysdate the 2nd Tuesday in that month.

Any help appreciated.

Trinitrotoluene · 17 Jun 2009 at 18:30

Can't be coincidence that the second tuesday in each month is MS's Patch Tuesday!

DireEmblem · 17 Jun 2009 at 18:42

Hi,

Below code should give you the Last day of the prior month, given todays SYSDATE:-

LAST_DAY(ADD_MONTHS(SYSDATE, -1))

Below will give you the next given tuesday:-

NEXT_DAY(SYSDATE,"TUESDAY")

Second tuesday should just be +7.

Hence combined results gives:-

NEXT_DAY(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),"TUESDAY") +7

Lead_Head · 18 Jun 2009 at 08:29

Bloody fantastic. Cheers guys.

DireEmblem · 18 Jun 2009 at 17:17

No bother, its a while since i've done any sys programming, and here's a good way to check out my knowledge.

SQL is a bit jiggery pokery in working with dates, but aboves the most simple way I can think of explaining such a way to do what you need to do.