how to put particular date in where clause in alasql

Question

See the below data on which i am doing query.

result = alasql("SELECT HourOfDay, Count(HourOfDay) TotalCount from ? where ReportedDate = DATE('1/20/2012') group by HourOfDay ", [data])

<table style="width:100%">
  <tr>
  <th>ReportedDate</th>	<th>HourOfDay</th>
  </tr>
  <tr>   
    <td>2012-01-20 09:30:00.000</td>
    <td>9</td>   
  </tr>
  <tr>
    <td>2012-01-21 09:30:00.000</td>
    <td>11</td>   
  </tr>
  <tr>
    <td>2012-01-11 09:30:00.000</td>
    <td>9</td>   
  </tr><tr>
    <td>2012-01-20 09:30:00.000</td>
    <td>5</td>   
  </tr><tr>
    <td>2012-01-20 09:30:00.000</td>
    <td>9</td>   
  </tr><tr>
    <td>2012-01-13 09:30:00.000</td>    
    <td>7</td>   
  </tr><tr>
    <td>2012-01-20 09:30:00.000</td>    
    <td>9</td>   
  </tr><tr>
    <td>2012-01-20 09:30:00.000</td>   
    <td>10</td>   
  </tr>
</table>

i need total count of HourOfDay where date is "1/20/2012", i am querying on this data with given query but i am not getting any result so please help me out,i am new to alasql.

This is my array

data=[
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'9'},
{'ReportedDate': '2012-01-21 09:30:00.000','HourOfDay':'10'},
{'ReportedDate': '2012-01-11 09:30:00.000','HourOfDay':'1'},
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'4'},
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'5'},
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'6'},
{'ReportedDate': '2012-01-20 09:30:00.000','HourOfDay':'8'},
{'ReportedDate': '2012-01-13 09:30:00.000','HourOfDay':'12'}];

Answer 1

I suggest using a custom function. It could be something like:

alasql.fn.theDate(s){
    return s.split(' ').shift();
} 

hours = alasql("VALUE OF SELECT SUM(HourOfDay) FROM ? WHERE theDate(ReportedDate) = '2012-01-20'", [data])

At some point you might find it interesting to get the sum of each day all at once:

totalHoursPerDay = alasql("SELECT theDate(ReportedDate) as date, SUM(HourOfDay) as hours FROM ? GROUP BY theDate(ReportedDate)", [data])