I have been given a MSQL Database Table that has been populated with 3 columns.
Column 1 has a Date, this has been set as a MSQL “Date” datatype.
Column 2 has a Time, this has been set as a MSQL “Time” datatype
Column 3 has intergers
I have tried to display the Data (Col 1 and col 3 only) in a Chart component but the Date as the X plot errors as an unknown value for X plot.
I tried to cast the “Date” to a datetime in the Data query which stops the error but fails to draw anything. The Dataset date column shows as 01/04/2012 00:00:00
If I remove the binding and manually manipulate the dataset Date to have an actual time eg. 01/04/2012 01:00:00 the chart works.
So getting to the question…
Is there a way to combine columns 1 and 2 to make a proper Datetime for the chart in a query or can I format just the Date (col 1) in a way that the chart likes?
When you cast the date column are you using the built in function system.db.dateFormat(date_cell,“yyyy-MM-dd HH:mm:ss”) ? Or are you casting with your SQL call?
I haven’t had any problems using dates that have “no actual time” like your ‘01/04/2012 00:00:00’.
Just saw that you did the casting it in your date query. Can you iterate through the list and do it in a script instead with the function I mentioned above?
[Log Date] is a “Date” datatype in MSQL
Using this query the Chart errors with" Illegal type for X value"
SELECT [Log Date] as t_stamp, [TR#1 Today permeate (kL)] FROM datalogA
This Query has no errors, but fails to display
SELECT cast([Log Date] as datetime) as t_stamp, [TR#1 Today permeate (kL)] FROM datalogA
I have a feeling it is not returning an actual date object. Click on the dataset view icon (next to the binding icon) on the Data property of the component. Once opened, click on the date column and tell me what the data type? You will see it below the table.
The error I was finding with just using the [Log Date] without casting it as a datetime was that the Dataset column was being shown as a string even though the SQL column is a Date type. Doc1.doc (123 KB)
It appears you have it setup correctly but for some reason the data is not showing up. Can you post a couple more screenshots from the chart customizer? One of the X-Axes tab with the axis selected and one of the of the Dataset Properties tab with the Data selected.
Ok, I don’t know why the data is not showing up. Everything looks like it is configured correctly. Any chance you can call our tech support or export that window in the designer and send it to us (not screenshots but the actual window)?
I tried setting the “Order by t_stamp ASC” and found that it errored until I removed a NULL row from the first line.
Looking through the data they have given me it appears that there are a couple of dates out of place… So without sorting by ascending it shows a blank chart.
thanks for your help… I guess it pays to fully check the data your given…