Hi Guys,

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?

Cheers for any help.

Tim...

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?

Can you post the SQL query that you are using?

[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.

ahhhhh...

You are correct, its still showing as a Date type??

Date is correct. It should be working ok. Let's check a couple of things:

1) What is the value of the Chart Type property on the graph?
2) If you open the chart customizer (through right click) and select the Dataset Properties tab what is the Renderer for your dataset?

1/ Default. XY Plot
2/ Default. XY Line/Shape renderer

Can you export a copy of the window with the error on it so we can see what is going on?

Doc attached with the chart setup.

No errors just a blank chart.

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.

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.

Screenshots attached

Cheers

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)?

Window Export attached

Cheers