CODEWITHSUNDEEP
sqlsqliteselect
Yang
Yang

Reputation: 6892

SQL get records in the same week

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.

Upvotes: 1

Views: 3408

Answers (3)

Justin Pihony
Justin Pihony

Reputation: 67075

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))

Upvotes: 3

sikander
sikander

Reputation: 2286

You can use BETWEEN to specify the records you want.

 SELECT * FROM records WHERE datecolumn between 'YYYY/MM/DD' AND 'YYYY/MM/DD'

Upvotes: 1

hkf
hkf

Reputation: 4520

Use the datediff() function:

datediff(ww,start_date,end_date) < 1

Upvotes: 2

Related Questions