Solved

DateTime column not converting to correct format?

Posted on 2015-01-12
19
82 Views
Last Modified: 2015-02-03
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:  

dbCreepData.Columns.Add("startTime", System.Type.GetType("System.DateTime"))

Open in new window


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 = 66

Open in new window


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.

            bsCreep.DataSource = EH.DataSet.Tables(0)
            bsCreep.Filter = "runNO = " & iCreepRunIDX
            dgvCreep.DataSource = bsCreep
            dgvCreep.AutoResizeColumns()

Open in new window

0
Comment
Question by:BlakeMcKenna
  • 9
  • 7
  • 3
19 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40545121
You can do the conversion at your application like:
Imports System.Globalization


Dim dt As DateTime = "1/12/2015 08:34 AM"
Console.WriteLine(dt.ToString("dd/MM/yyyy hh:mm:ss", CultureInfo.InvariantCulture))

of course replacing dt here with your value from SQL.
0
 

Author Comment

by:BlakeMcKenna
ID: 40545350
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.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40545461
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.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40545511
I think, all you have to do is treat this date as string in your gv not as a date.


This comment is revised.
0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 334 total points
ID: 40545577
I did test vartype in SQL Server:
declare @dt datetime
set @dt='11/11/2015 11:15:16 PM'
select convert(varchar(19), @dt, 109) 

if cast(sql_variant_property(convert(varchar(25), @dt, 109),'BaseType') as varchar(19))  = 'varchar'
print 'yes'
else
print 'no'

Open in new window


This prints yes meaning it is in string in SQL Server. Make sure your gv property for this date column  is also set to text.
0
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 166 total points
ID: 40546975
Why do you need to convert in SQL Server? Why not return the columns as they are?
SELECT creep_ID,runNO,intervalPoint,output,[error],startTime,printOnCert 
FROM TEST_DETAILS_Creep 
WHERE creep_ID = 66

Open in new window

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40546989
Because he want to display like 23/12/2015 08:34:08 not 11/12/2015 08:34:08 PM.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40546998
The display is on the datagrid not in SQL Server. He can format it in the datagrid.
0
 

Author Comment

by:BlakeMcKenna
ID: 40547108
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
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40547185
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.
0
 

Author Comment

by:BlakeMcKenna
ID: 40547227
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.
0
 

Author Comment

by:BlakeMcKenna
ID: 40547228
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 Sub

Open in new window

0
 

Author Comment

by:BlakeMcKenna
ID: 40547230
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")

Open in new window

0
 

Author Comment

by:BlakeMcKenna
ID: 40585156
Where is the "Request Attention" button?
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 334 total points
ID: 40585214
see:
RequestAttention
0
 

Author Comment

by:BlakeMcKenna
ID: 40586198
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...
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40586219
It's really there, Blake. Just check on the top of the first comment on this question.
0
 

Author Comment

by:BlakeMcKenna
ID: 40587178
Now I see it. I didn't scroll down far enough.

Thanks Vitor!
0
 

Author Closing Comment

by:BlakeMcKenna
ID: 40587188
This is no longer an issue. Thank you guys for your inputs. We ended up removing this column from the dgv altogether...
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now