CODEWITHSUNDEEP
mysqlsql
Henry
Henry

Reputation: 35

How to get the week number based on today date

I am trying to select the current date based on the weeknumber in the weekstu table.

so lets says we are in week 1 which started Monday the 25/11/2013. I am trying to get that date based on using today date which is the 28/11/2013. Week 2 will be the Monday the 2/12/2013. The query I have below doesn't return any record. Is this possible to get the beginning of the week based on today date?

table:weekstu
weekid
startdate
setid
weeknumber
startdate

Table:week
weekid
setid


    SELECT * from weekstu ws
   JOIN week w ON ws.setid = w.setid AND ws.weekid ON w.weekid
   WHERE ws.weeknumber = `1` AND startdate = CURRENT_TIMESTAMP

Upvotes: 0

Views: 505

Answers (2)

Krish R
Krish R

Reputation: 22711

DATE_FORMAT(date,format)

%b Abbreviated month name (Jan..Dec)

%W Weekday name (Sunday..Saturday)

DATE_FORMAT(NOW(),'%W')

REF: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Upvotes: 0

Andomar
Andomar

Reputation: 238086

You can determine the beginning of the week like:

select adddate(curdate(), interval 1 - dayofweek(curdate()) day)

Note that a condition like:

startdate = CURRENT_TIMESTAMP

Will only match rows that have the exact current timestamp up to the millisecond. Unlike curdate(), current_timestamp contains time information as well as date information.

Upvotes: 2

Related Questions