6

I have a table with a bunch of dates (option maturity dates to be precise). I need to query this database to find the last day of a specific week that is stored in the table.

All I will be given to query this table is the year, the month and the specific week. And based on this I need to find the date that is stored in the table that matches this.

I've created the following query to find this specific date March 28 2013

SELECT M_SETNAME, M_LABEL, M_MAT FROM OM_MAT_DBF
WHERE M_SETNAME = 'IMM_OSET  '
AND MONTH(M_MAT) = 3
AND YEAR(M_MAT) = 2013
AND ((DATEPART(day,M_MAT)-1)/7 + 1) = 5

Do you guys have any idea of how I can change the last condition so that March 28th will be considered the 5th week of the month and not the 4th week as it is currently doing.

user3284817
  • 61
  • 1
  • 2
  • Hmmm, March 28th is the *fourth* week of the month (first week, 1-7; second, 8-14; third, 15-21; fourth 22-28). What is your logic for counting weeks in a month? – Gordon Linoff Feb 10 '14 at 20:21
  • My logic considers the last day of the week as a week. So, March 1st falls on a Friday therefore my query will give me March 1, 2013 when I set the last condition to 1. So basically March 1 - 1st week March 8 - 2nd week March 15 - 3rd week March 22 - 4th week March 28 - 5th week (not 29 b/c the table only has March 28th as for that week and not Mar 29th) – user3284817 Feb 10 '14 at 20:28
  • How do you count a month where the first day is Saturday? (There is no Friday in that first week!) Also, what is the "last day of the week"...some calendars start on Sunday (Sun-Sat) and others start on Monday (Mon-Sun). – Whitzz Feb 13 '14 at 09:18

1 Answers1

1

You can also use DATEPART to get the number of the week (in the year), but then, you could also get the 1st of each month, and take the week too so you can have: WEEK OF MY DATE - WEEK OF FIRST DAY FOR THIS MONTH + 1.

Here you have an example...

DECLARE @Dt datetime

SELECT @Dt='03-28-2013'

SELECT DATEPART( wk, @Dt) - DATEPART( wk, Convert(Date,Convert(varchar(4),YEAR(@Dt))
+ '-' + Convert(varchar(2), MONTH(@Dt))
+ '-' + Convert(varchar(2), 1))) + 1

EDIT: Also, looking at your code, you could add the CEILING. If the result == 2.7, it means it passed the 2nd week, however, it gets rounded to 2 when it should actually be 3.

If you add the CEILING and the CONVERT to decimal should work..

SELECT  MONTH(@Dt), 
        YEAR(@Dt), 
        ((CEILING(Convert(decimal,DATEPART(day,@Dt)-1)/7)) + 1)
Yumei De Armas
  • 386
  • 2
  • 6