Identifying week range by todays date

[werosen1]

I'm trying to write an SQL query that identifies today's date, and then provides the date range for that date's week, so I can return all my database records that fall in that week's date range.

Is there a way to do this?

Regards

 

[r937]

unfortunately, date arithmetic is probably the one area where ANSI SQL is least implemented in the various database systems, so how to do it will likely depend on which one you're using

please re-post your question in the appropriate forum



r937.com | rudy.ca

 

[JarlH]

ANSI SQL compliant solution:

SELECT * FROM sometable
WHERE datcol BETWEEN (current_date - CAST(dayofweek(current_date)-1 AS interval day))
             AND     (current_date + CAST(7-dayofweek(current_date) AS interval day))

 

[r937]

ANSI SQL has a dayofweek function???

whoa, who knew

what day of the week is monday?

r937.com | rudy.ca

 

[JarlH]

Sorry about that, of course DAYOFWEEK is *not* an ANSI SQL function.

Don't know what I was thinking...