Click here to Skip to main content
15,851,387 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I have the following codes in VB. I have a variable in DateTime and I tried to convert the format of returned value from database into yyyy-MM-dd HH:mm:ss I have tried to convert it into string so that I may alter its format but seems like it does not work as expected. Below is my codes:

VB.NET
Dim woStartDate As DateTime
While readerSQL.Read()
      If Not IsDBNull(readerSQL.Item("WOStartDate")) Then
         woStartDate = readerSQL.Item("WOStartDate") '11/8/2016 1.52 PM
         woStartDate = woStartDate.ToString("yyyy-MM-dd HH:mm:ss") '2016-11-08 13:52:21(expected result)
      Else
         woStartDate = DateTime.Now
      End If
End While



The value stay in the same original format after conversion. I have used this function plenty of times(in other program) and it seems not have any problem converting.

What I have tried:

1. Tried to convert readerSQL.Item("WOStartDate") into string directly but returns error.
Posted
Updated 7-Nov-16 23:07pm
Comments
Ralf Meier 8-Nov-16 1:10am    
Are you sure that your code-part after the IF is worked ...?
I suppose that you get a IsDBNull from the Item and because of this your code workes in the ELSE-branch.
Could yould please check and confirm (or not confirm) this by debugging ...?

When you try to convert the Item direct and it Returns an Error ... which kind of Error do you get and ... what is the Content of the Item ? I think this is the same Problem as above ...
Jamie888 8-Nov-16 1:14am    
Yes sir, I have debugged my codes before. It indeed flow into the IF part. The value is GETDATE() which is in 11/8/2016 1.52 PM format. Then it comes to woStartDate.ToString("yyyy-MM-dd HH:mm:ss") part for conversion but nothing happen.
Ralf Meier 8-Nov-16 2:24am    
see my Solution (2).

try to use this :

VB
woStartDate  =  DateTime.ParseExact(woStartDate , "MM/dd/yyyy h:mm:ss tt",
           Globalization.CultureInfo.InvariantCulture)
 
Share this answer
 
Sorry ... I read the code in the wrong way.
Try this code :

VB
Dim woStartDate As DateTime
dim myStartDate as string
While readerSQL.Read()
      If Not IsDBNull(readerSQL.Item("WOStartDate")) Then
         woStartDate = readerSQL.Item("WOStartDate") '11/8/2016 1.52 PM
         myStartDate = woStartDate.ToString("yyyy-MM-dd HH:mm:ss") '2016-11-08 13:52:21(expected result)
      Else
         myStartDate = DateTime.Now.tostring
      End If
End While


A Date or a DateTime is simply this.
Only a string could hold a different formatting ...
 
Share this answer
 
Comments
Jamie888 8-Nov-16 20:15pm    
Thank you sir for your information. I have done it according to your suggestion and it is working fine now. Thank you again.
A DateTime variable doesn't have a format, it represents a point in time. It only gains a format when you convert it to a string. When you simply ask a DateTime variable to show itself as a string it chooses the format defined in the system settings of the OS, if you want it in a specific format you need to supply that format via ToString. The DateTime variable itself though doesn't have a format, what you are really seeing when you show it is its default representation.

Basically, you can't give DateTime a format, you can't give dates in SQL a format, you only give dates a format when you convert them to string so leave them as they are and convert them when you need to display them.
 
Share this answer
 
Comments
Ralf Meier 8-Nov-16 6:12am    
Very good described ... but did you have had a look at my Solution ...?
Jamie888 8-Nov-16 20:14pm    
Yes sir. Thank you for your explanation. It had clear my confusion. I have studied Mr. Meier(solution 2) solution and modified according to my need. It is working now.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900