0

I have a line chart that works fine with the following query:

SELECT peakdate, peakusage
FROM peaktable
WHERE peakDate BETWEEN '#arguments.dtBegin#' AND '#arguments.dtEnd#'             
ORDER BY peakdate

The date output looks something like: 2014-01-01 00:00:00.0 and I have no trouble converting this to milliseconds for the datetime chart.I use the JavaScript getTime() function to convert the date and all is well.

Now, instead of displaying all the data points on the chart, I need to display the MAX peak for each day. Here is the query I wrote that works well in SQL Server:

SELECT convert(varchar(10), peakdate, 120) as peakdate , MAX(peakusage)
FROM peaktable
WHERE peakDate BETWEEN '#arguments.dtBegin#' AND '#arguments.dtEnd#'
GROUP BY convert(varchar(10), peakdate, 120)     
ORDER BY convert(varchar(10), peakdate, 120)

The date output of this looks like 2014-01-01 with no time on the end. I kept it at varchar(10) in order to group at the date level. Unfortunately the JavaScript getTime() function throws an error because there is no time element attached to the date. So I thought I was being clever and I concatenated 00:00:00.0 to the end of the date in the SQL statement, but it still is throwing an error. I guess JavaScript did not see it as a date:

SELECT CONCAT(convert(varchar(10), peakdate, 120),' 00:00:00.0') as peak , MAX(peakusage)
FROM peaktable
WHERE peakDate BETWEEN '#arguments.dtBegin#' AND '#arguments.dtEnd#'
GROUP BY convert(varchar(10), peakdate, 120)     
ORDER BY convert(varchar(10), peakdate, 120)

Does anyone have any ideas on how to use a date object with no time element in a Highcharts datetime chart? The next query I am going to have to chart is display the peak point for the month. So the date format will look like yyyy-mm.

Water Monkey
  • 93
  • 1
  • 1
  • 3
  • "It is showing an error". What error? – Halvor Holsten Strand Jul 15 '14 at 21:14
  • Right now it is returning a string for peak. You will need to convert it back into a datetime. Although for the record, I'm not exactly sure why you would want to do this. You might as well just select the date time. – Jenn Jul 15 '14 at 21:16
  • So the problem is with returning data in correct form (I mean timestamp) or with correct query to database? How your json looks like? – Sebastian Bochan Jul 16 '14 at 09:58
  • In your sql query `SELECT CONCAT(convert(varchar(10), peakdate, 120),' 00:00:00.0') as peak ...,` **instead of `peak`** shouldn't it be `peakdate` ?? – Rahul Gupta Jul 16 '14 at 10:57
  • @Jenn your suggestion worked; recasting the date as a datetime worked. The purpose of this is to reduce the noise on a chart. The Group By conversion of the date returns a date object that is unreadable by the getTime() function. So, I thought by using the concatenation I would be able to mimic a datetime object; JavaScript saw this as a string. But by recasting the string as a datetime just before using the getTime() everything worked. – Water Monkey Jul 16 '14 at 22:12
  • I guess I just don't understand why you aren't casting the datetime as a string and passing that back to the Javascript. Then, change your Javascript variables to be dates. The goal is to remove the time, right? – Jenn Jul 17 '14 at 14:20

0 Answers0