STR_TO_DATE, use current date to display results for current week

[fr0s1y]

Ok,

i need a report displayed dynamically, its for a rota system. When the user logs in the need to be able to see their shifts for the current week, i can generate the report no problem and i have a general idea of the sql i need to use just not HOW to use it, heres what i got:

SELECT CONCAT(stf.forename,' ',stf.surname) AS Name
, date_format(STR_TO_DATE (CONCAT(YEAR(NOW()), WEEKOFYEAR(NOW()), 'Monday'),'%X%V %W')) AS `Date`
, CONCAT(TIME_FORMAT(shf.start_time,'%h:%i %p'),' - ',TIME_FORMAT((shf.end_time),'%h:%i %p')
) AS Shift
from

now that gives me the results i want, i just need to display them. I figure i need to work with these statements somehow:

SELECT STR_TO_DATE(CONCAT(YEAR(NOW()), WEEKOFYEAR(NOW()), ' Monday'), '%X%V %W');
SELECT STR_TO_DATE(CONCAT(YEAR(NOW()), WEEKOFYEAR(NOW()), ' Sunday'), '%X%V %W');

I need the weekdays to be in the range of monday and sunday every week, any ideas guys?

 

[ss7thirty]

Actually just realized this was mySQL code, Datepart will not work. Possibly consider using DayName function not sure what you are trying to accomplish exactly with that STR_TO_DATE function.

 

[fr0s1y]

hi, its ok i figured out what it was i was doing,

i used STR_TO_DATE to give my separate columns for the days of the week, i was then able to use those columns generated dynamically in the report in my VB.NET application, all sorted!!