1

I have a table that contains records with a column indicates the Date. Given a record, I would like to select all records that are in the same week as the record. How can SQL do that?

I should say that I'm using SQLite.

Yang
  • 6,682
  • 20
  • 64
  • 96
  • 1
    Seems like you'd have to use something like this http://www.objectreference.net/post/SQL-Find-last-week-date-range.aspx But modified so that the start date is based off the date of the record you've selected. – George Clingerman Mar 23 '12 at 04:59

3 Answers3

3

You can use DATEPART with wk to get the current week. Then just check for equality. In this case, I have also checked yy to make sure that you do not check the year of a previous week.

SELECT * 
FROM TABLE
WHERE DATEPART(wk, TABLE.DATECOLUMN) 
        = DATEPART(wk, (SELECT DATECOLUMN FROM TABLE WHERE ID = GivenID))
    AND DATEPART(yy, TABLE.DATECOLUMN) = DATEPART(yy, (SELECT DATECOLUMN FROM TABLE WHERE   ID = GivenID))

UPDATE FOR SQLITE

To do this in SQLLite, Refer to this SO question and then this article that states %W is what you use to get week and %Y for year. Which gives you:

SELECT * 
FROM TABLE
WHERE STRFTIME('%W', TABLE.DATECOLUMN) 
        = STRFTIME('%W', (SELECT DATECOLUMN FROM TABLE WHERE ID = GivenID))
    AND STRFTIME('%Y', TABLE.DATECOLUMN) 
        = STRFTIME('%Y', (SELECT DATECOLUMN FROM TABLE WHERE ID = GivenID))
Community
  • 1
  • 1
Justin Pihony
  • 66,056
  • 18
  • 147
  • 180
2

Use the datediff() function:

datediff(ww,start_date,end_date) < 1
hkf
  • 4,440
  • 1
  • 30
  • 44
1

You can use BETWEEN to specify the records you want.

 SELECT * FROM records WHERE datecolumn between 'YYYY/MM/DD' AND 'YYYY/MM/DD'
sikander
  • 2,286
  • 16
  • 23