Show timestamp and not date in chart - SQL

Question

I'm working on a data handling page for a customer.

I've made a chart that shows data from my SQL server.
On my x-axis I have a date but the whole column contains also a timestamp e.g. "08/01/2019 14:34", but is not visible.

As seen on the linked picture below, how do I format the x-axis value to only show the time, or the whole value from the dbo, if possible?

Do I have to format the size of the chart on the screen, if so, how do I do that? I've tried looking for an option in the chart settings.

Here's a snippet of the code:

Private Sub dgvSiteChart() 'Load data from dbo
    DateandTimeString24H = "'" & Date.Now.Year & "-" & Date.Now.Month & "-" & Date.Now.Day & " 00:00:00.000'" & " And " & "'" & Date.Now.Year & "-" & Date.Now.Month & "-" & Date.Now.Day & " 23:59:59.000'"
    SQLCon = New SqlConnection
    SQLCon.ConnectionString = "Data Source=" & System.Net.Dns.GetHostName() & "\KVMSQL;Initial Catalog=MHA;User ID=#####;Password =######"
    Dim READER As SqlDataReader

    Try
        SQLCon.Open()
        Dim Query As String
        Query = "SELECT DateAndTime,Tagindex,Val FROM dbo.FT WHERE tagindex=0 and DateAndTime between " & DateandTimeString24H & "ORDER BY DateAndTime"
        SQLCmd = New SqlCommand(Query, SQLCon)
        READER = SQLCmd.ExecuteReader
        While READER.Read
            chrtReportMchn.Series("LOG").Points.AddXY(READER("DateAndTime"), READER("Val"))
        End While
        SQLCon.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        SQLCon.Dispose()
    End Try
End Sub

Date and time format is dd/MM/yyyy HH:mm(24H).

UPDATE: 10/1-19

I've figured it out, with the help of @Markus with his comment. The code is edited with FORMAT(CAST(DateAndTime as time), 'hh\:mm\ ') put in front of the DateAndTime.

Try
    SQLCon.Open()
    Dim Query As String
    Query = "SELECT FORMAT(CAST(DateAndTime as time), 'hh\:mm\ ') DateAndTime,Tagindex,Val FROM dbo.FT WHERE tagindex=0 and DateAndTime between " & DateandTimeString24H & "ORDER BY DateAndTime"
    SQLCmd = New SqlCommand(Query, SQLCon)
    READER = SQLCmd.ExecuteReader
    While READER.Read

when you only want to show the time, why not cast the datetime column in the select query — Markus, Jan 09 '19 at 14:20
like so FORMAT(CAST(DateAndTime as time), N'hh\:mm\:ss') [time] — Markus, Jan 09 '19 at 14:24
Hi Markus. Thanks for the reply. I'm pretty new at this, so I can't seem to work out where I should put the FORMAT(CAST(DateAndTime as time), N'hh\:mm\:ss') [time] line in my code. I've tried it in the Query but it reponded with a blank chart, and an ERROR occurs when I try it out in SQL Server mngt studio — MHammer, Jan 10 '19 at 06:47

score 0 · Answer 1 · answered Jan 18 '19 at 09:08

You need to Dispose all database-related objects (connections, commands, readers). It is better to use Using for that instead of doing it manually in Finally.
You have to use parameters instead of string concatenation. That way you also don't lose the last second of the day (between 23:59:59 and 00:00:00 of the next day).
You can use a dot in the connection string to represent the local computer.
You don't need to return TagIndex if you are not using it.
You can format the date on the client.

Private Sub dgvSiteChart()
    Try
        Using SQLCon = New SqlConnection("Data Source=.\KVMSQL;Initial Catalog=MHA;User ID=#####;Password =######")
            SQLCon.Open()
            Using SQLCmd = New SqlCommand("SELECT DateAndTime,Val FROM dbo.FT WHERE tagindex=0 and DateAndTime >= @from_inclusive and DateAndTime < @to_exclusive ORDER BY DateAndTime", SQLCon)
                Dim captured_date = DateTime.Today ' Because it may change if calling this around midnight

                SQLCmd.Parameters.Add("@from_inclusive", SqlDbType.DateTime).Value = captured_date
                SQLCmd.Parameters.Add("@to_exclusive", SqlDbType.DateTime).Value = captured_date.AddDays(1)

                Using READER = SQLCmd.ExecuteReader()
                    While READER.Read()
                        chrtReportMchn.Series("LOG").Points.AddXY(CType(READER("DateAndTime"), Date).ToShortTimeString(), READER("Val"))
                    End While
                End Using
            End Using
        End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Show timestamp and not date in chart - SQL

1 Answers1