DateTime column not converting to correct format?
I have a Windows app that for some reason isn't converting a DateTime column over to the correct format. The column is defined in a SQL Server 2008 DB as a "DateTime" datatype. In my VB.Net app, I've created a table with it's associated columns and defined the particular date column the same as such:
My SQL statement looks like the following. I tried 2 different ways of retrieving the date. These statements were ran in the SSMS.
The problem that I'm having is that the App needs to display the "startTime" like this: 1/12/2015 08:34:08
What it's doing is displaying the "startTime" like this: 1/12/2015 08:34 AM
As far as using the CONVERT statement in my SQL script, I've tried the format of 109 and it still doesn't work. I'm not doing any kind of data manipulation when I assign the DataTable to the DataGridView's datasource. Here is what that logic looks like. "bsCreep" is just a BindingSource.
dbCreepData.Columns.Add("startTime", System.Type.GetType("System.DateTime"))My SQL statement looks like the following. I tried 2 different ways of retrieving the date. These statements were ran in the SSMS.
SELECT creep_ID,runNO,intervalPoint,output,[error],CONVERT(DateTime,startTime,9) AS startTime,printOnCert FROM TEST_DETAILS_Creep WHERE creep_ID = 66
SELECT creep_ID,runNO,intervalPoint,output,[error],CONVERT(DateTime,startTime,109) AS startTime,printOnCert FROM TEST_DETAILS_Creep WHERE creep_ID = 66The problem that I'm having is that the App needs to display the "startTime" like this: 1/12/2015 08:34:08
What it's doing is displaying the "startTime" like this: 1/12/2015 08:34 AM
As far as using the CONVERT statement in my SQL script, I've tried the format of 109 and it still doesn't work. I'm not doing any kind of data manipulation when I assign the DataTable to the DataGridView's datasource. Here is what that logic looks like. "bsCreep" is just a BindingSource.
bsCreep.DataSource = EH.DataSet.Tables(0)
bsCreep.Filter = "runNO = " & iCreepRunIDX
dgvCreep.DataSource = bsCreep
dgvCreep.AutoResizeColumns()ASKER
I'd rather find out why it's doing what it's doing.
When I run the query in SMSS, the data looks as it should. It only looks different once it's loaded into the DataGridView.
When I run the query in SMSS, the data looks as it should. It only looks different once it's loaded into the DataGridView.
Basically dates, regardless of their format, are the same. GV takes the dates and based on its present format property treat it accordingly. This means the format in SQL doesn't matter unless you treat it (convert it) to string. That way, the gridview column being also string, will show it as you want it to.
I think, all you have to do is treat this date as string in your gv not as a date.
This comment is revised.
This comment is revised.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Because he want to display like 23/12/2015 08:34:08 not 11/12/2015 08:34:08 PM.
The display is on the datagrid not in SQL Server. He can format it in the datagrid.
ASKER
If you look at the Image I've attached, please see the "Time" column. I just need for the "Seconds" to show up in this format. When these records are being added thru the VB.Net app, I'm simply assigning "Now" to a variable that is defined as DateTime and when it displays on the DGV, it appears with the "Seconds" (just like I want it).
The difference is when I retrieve this record from the DB, it comes out like you see in the attached image.
DataGridView.JPG
The difference is when I retrieve this record from the DB, it comes out like you see in the attached image.
DataGridView.JPG
What is the property of your Time column in the gv? It should be text not date. This is because you already have converted it to string in the sql.
If Time column has property of date not text, then it grabs the date value from sql and treats it as date and in the absence of user defined format, it gets displayed in default format which you see in the image you have attached.
The sure way for all these is to add format at the gv as mentioned in my second post at the top.
If Time column has property of date not text, then it grabs the date value from sql and treats it as date and in the absence of user defined format, it gets displayed in default format which you see in the image you have attached.
The sure way for all these is to add format at the gv as mentioned in my second post at the top.
ASKER
I had it defined as System.DateTime but then I dropped the DataType altogether. It should default as System.String. Even though I did this....it still displays the same as in the Image.
ASKER
Here is the routine where I setup the HeaderText.
Private Sub BuildCreepGrid()
Try
EH.ErrorMessage = String.Empty
dgvCreep.Columns(0).HeaderText = "Creep ID"
dgvCreep.Columns(1).HeaderText = "Run"
dgvCreep.Columns(2).HeaderText = "Interval"
dgvCreep.Columns(3).HeaderText = "Output (" & cmbOutputUnit.Text & ")"
dgvCreep.Columns(4).HeaderText = "Error (%)"
dgvCreep.Columns(5).HeaderText = "Time"
dgvCreep.Columns(6).HeaderText = "printOnCert"
dgvCreep.Columns(0).Visible = False
dgvCreep.Columns(1).Visible = False
dgvCreep.Columns(6).Visible = False
Catch ex As Exception
EH.ErrorMessage = "frmCalibration_3/BuildCreepGrid() - " & ex.Message & "...Contact Engineering!" & "~E"
End Try
End SubASKER
I even have a routine that formats that "Time" cell. I do it like this:
dRow.Cells(5).Value = String.Format(dRow.Cells(5).Value, "General Date")ASKER
Where is the "Request Attention" button?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm gonna guess it's somewhere just not where your saying. I looked at the top of this page at my original post as well as exited this post and selected it from the sidebar and it still wasn't there...
It's really there, Blake. Just check on the top of the first comment on this question.
ASKER
Now I see it. I didn't scroll down far enough.
Thanks Vitor!
Thanks Vitor!
ASKER
This is no longer an issue. Thank you guys for your inputs. We ended up removing this column from the dgv altogether...
Imports System.Globalization
Dim dt As DateTime = "1/12/2015 08:34 AM"
Console.WriteLine(dt.ToStr
of course replacing dt here with your value from SQL.