sql server - MSSQL datetime datetime in php -

i have problem think exist couple solutions.

scenario have build intranetpage internal performance kpi's visible office. data extracted mssql database , shown on webpage in combination php, data , converted array google chart api can create beautiful charts there. far, good!

problem have chart created tickets last 7 days shown (in reverse order). number of tickets created mssql count, 1 number each day (today - 7) in total.

but on days, no tickets created saterday or sunday. thats things go wrong.

today thursday , shows:

day          amount thursday     25 friday       10 monday       30 (etc) tuesday      15 (day before)  wednesday    20 (yesterday) thursday     50 (today) 

instead of

saturday      0 sunday        0 monday       30 tuesday      15 wednesday    20 thursday     50 

the problem there no records (tickets) create-date on saturday or sunday, mssql cant count them.

php calculates dates (also today -7) shown beneath axis in chart, php include saterday , sunday, axis titles not representive data axis shows.

possible solution 1 solution somehow mssql shows saterday , sunday in result table. dont think possible, because records dont exist.

other solution retrieve datetime used in mssql query , parse them intranetpage google charts can display dates instead of php created dates. "convert datetime class string" error.

because datetime in mssql datetime type , not varchar type. after trying lot of things , searching more on google , stackoverflow, can succesfull convert datetime varchar. part take todays date , go 7 days, doesnt work anymore.

my php (and query) follow:

---

$sql = "                                                                  select  top 7     cast(bb_casestartingtime date) date, count(*) totalrows             crm_mscrm.dbo.filteredincident            bb_casestartingtime > (convert(date, getdate() - 7)) group cast(bb_casestartingtime date) order date  "; 

---

$stmt = sqlsrv_query( $conn, $sql );                                     if( $stmt === false) {die( print_r( sqlsrv_errors(), true) );}  $x=5;  while( $row = sqlsrv_fetch_array( $stmt, sqlsrv_fetch_assoc) )       {   **$datevalue = date("y-m-d", strtotime(" -$x day"));**         echo "[',".$row[**date**].$row['totalrows'].",],";         $x--;        } 

i broke 2 pieces of code in half,

the table looks (at least, interesting part):

case id   bb_casestartingtime 01        2016-11-10 15:30:45.000 02        2016-11-10 16:22:10.000 03        2016-11-09 15:02:15.000 

i count amount of occurrences date (bb_casestartingtime) total. today have today 2 records yesterday 1 record

acutal question can me extraced date-time used in query?

i use one table called: filteredincident there way convert datetime bb_casestartingtime varchar (so php can extract it) still count amount of occurrences per day?

if information needed, please ask

thanks in advance!

(i rewrote entire question, because wrote initial post in rush)

you achieve you're after creating date range table , can left join it, 0 days no tickets. can run code in isolation test , should give can adapt:

-- table dummy data create table #filteredincident (bb_casestartingtime datetime)  -- insert dummy data has gaps , more 7 days insert #filteredincident         ( bb_casestartingtime ) values  ( '20161110 12:00'),         ( '20161110 11:00'),         ( '20161109 10:00'),         ( '20161108 13:00'),         ( '20161107 09:00'),         ( '20161104 07:00'),         ( '20161104 05:00'),         ( '20161104 16:00'),         ( '20161103 18:00'),         ( '20161103 19:00')  -- create date range table last 7 days create table #daterange ( dateval date )  -- insert last 7 days insert #daterange         ( dateval ) values  ( getdate() )        ,( dateadd(d,-1,getdate()))        ,( dateadd(d,-2,getdate()))             ,( dateadd(d,-3,getdate()))             ,( dateadd(d,-4,getdate()))             ,( dateadd(d,-5,getdate()))             ,( dateadd(d,-6,getdate()))       -- select * #filteredincident -- uncomment see data -- select * #daterange        -- uncomment see date range data  select count(fi.bb_casestartingtime) totalrows, dr.dateval #daterange dr left join #filteredincident fi on dr.dateval = cast(fi.bb_casestartingtime date) group dr.dateval order dr.dateval  drop table #filteredincident drop table #daterange       

produces:

totalrows   dateval 3           2016-11-04 0           2016-11-05 0           2016-11-06 1           2016-11-07 1           2016-11-08 1           2016-11-09 2           2016-11-10 

note: i'd recommend moving sql code in stored procedure opposed building string execute doing.