Solved

Date conversion using .ToString vs Format

Posted on 2013-01-21
13
254 Views
Last Modified: 2013-01-28
I am using a System.Data.SQLClient object to return a SQL field ProducedDt (SmallDateTime in SQL 2008R2).
 Dim CoConn As New System.Data.SqlClient.SqlConnection
        Dim CoCmd As New System.Data.SqlClient.SqlCommand
        Dim CoReader As System.Data.SqlClient.SqlDataReader = Nothing
...
If CoReader.IsDBNull(7) Then ProducedDtString = "null" Else ProducedDtString = CoReader.Item("ProducedDt").ToString
...

Open in new window


I would like to store the returned value in a string. When I use just .ToString I get the time as well as a date. And the date is not formatted the way I would like.

I can use
Format(CoReader.Item("ProducedDt"),"MM/dd/yyyy")

Open in new window



to get the results I want.
But if I try
CoReader.Item("ProducedDt").ToString("MM/dd/yyyy")

Open in new window

I receive an error, "Conversion from string "MM/dd/yyyy" to an Integer is not valid"


Is there a way to use .ToString to format a SmallDateTime field from SQL server into the format "MM/dd/yyyy"?

Thanks,
pat
0
Comment
Question by:mpdillon
  • 5
  • 5
  • 2
13 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38802804
If you put a breakpoint on that line, and execute the following in the Immediate Window (Ctrl-Alt-I ["eye"]):

?CoReader.Item("ProducedDt").GetType()

...what does the Immediate Window show?
0
 

Author Comment

by:mpdillon
ID: 38803142
System.runtimeType: ("DateTime" FullName = "System.DateTime")

Does that help?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38803168
What is the entire line that errors?
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 38803204
Why can't you do this:

If CoReader.IsDBNull(7) Then ProducedDtString = "null" Else ProducedDtString = format(CoReader.Item("ProducedDt"), "MM/dd/yyyy")
0
 

Author Comment

by:mpdillon
ID: 38803771
UnifiedIS,
As I mentioned in my post, I can do that. I am just trying to gain a better understanding of the ToString function.

Since my post I have also learned that:
Dim L as Long = 77
Dim D as Decimal = 55.44
Dim LString as string = String.Empty
DimDString as string = String.Empty

LString = L.ToString()   'Works fine
LString = Format(L,"#")  'Works fine
LString = L.ToString("#")  'Causes an error

DString = D.ToString("#.##")  'Does Not cause an error
DString = D.ToString()  'Works fine
DString = Format(D,"#.##")  'Works Fine

I do not understand why one way works and another does not. So I thought I would ask.

pat
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38804100
The problem doesn't appear to be with the ToString method. The problem is that you are trying to assign a string to an integer variable. This is why I asked to see the full line of code  : )
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:mpdillon
ID: 38805511
kaufmed,

Thank you for the explanation but I do not understand it. I am going to write what I think is happening and I would greatly appreciate it if you would correct my thinking.
ProducedDtString = CoReader.Item("ProducedDt").ToString("MM/dd/yyyy")

Open in new window


I thought SQL smalldatetime is stored as a number. I am not sure what is returned by the ExecuteSQLReader command. But I assume it is a number. I want to use the ToString method to convert this Number to a string.  That converted string is then assigned by the equal sign to the String varaible ProducedDtString. I thought that when I use .ToString("MM/dd/yyyy"), I was telling the VB ToString function to format the conversion of the number in a specific manner. To my mind I am assigning a String to a String.

I do not understand what step assings a string to an integer. Of course, it is my lack of understanding of this basic concept that is the reason I am having difficulty underestanding why my ToString("MM/dd/yyyy") does not work.

Thanks in advance for taking the time to explain further.

pat
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 38805604
I thought SQL smalldatetime is stored as a number. I am not sure what is returned by the ExecuteSQLReader command.
Are you sure you're not sure? I had you post it above  ; )
System.runtimeType: ("DateTime" FullName = "System.DateTime")


I thought that when I use .ToString("MM/dd/yyyy"), I was telling the VB ToString function to format the conversion of the number in a specific manner.
You are, but that format doesn't work for numbers; it works for dates. Once you get to .NET land, dates are stored as DateTime structs (disregard storing a date as a string or a tick count for the sake of this discussion). A DateTime struct does permit the above format string to be used.

I do not understand what step assings a string to an integer.
Actually, upon taking another look, I think the problem is that you need a cast--there is no assignment going on. Try this instead:

DirectCast(CoReader.Item("ProducedDt"), DateTime).ToString("MM/dd/yyyy")

Open in new window

0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 38805728
Looks like you can use .tostring with a datetime but not without converting to a .net datetime.

you could do this conversion to date and then use the .tostring with a formatting mask

ProducedDtString = cdate(CoReader.Item("ProducedDt")).ToString("MM/dd/yyyy")

this will return the short date:
ProducedDtString = cdate(CoReader.Item("ProducedDt")).ToString("d")

Other standard format specifiers are on msdn at
http://msdn.microsoft.com/en-us/library/az4se3k1(VS.90).aspx
0
 

Author Closing Comment

by:mpdillon
ID: 38809642
Here is what I am taking away so far.
The Reader returns a System.DateTime type object. The ToString function will return a String representation of the System.DateTime object. However, the ToString the default format of the ToString function cannot be overridden which is what I was trying to do with “ToString(“MM/dd/yyyy”).
The same behavior is observed when a BigInt or Int is returned from SQL server by the reader. But oddly, at least to me, when a Decimal is returned from SQL Server the default format can be overridden with formats such as: ToString(“#”), ToString(#0.##”), etc.
The solution to the Date issue is to DirectCast the Sytem.DateTime object that is returned to a DateTime.  Of course, I am curious about the difference between System.DateTime and DateTime.
I did some reading on CType and DirectCast. My take way was that DircetCast is more specific that CType functions and therefore a bit more finicky. But it is appropriate for this discussion.
Thanks everyone for contributing.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38809947
Of course, I am curious about the difference between System.DateTime and DateTime.
There is no difference. System.DateTime is the fully-qualified name. If you have an "Imports System" at the top of your file, then you can simply do:

Dim d As New DateTime

Otherwise, you have to fully qualify the type name:

Dim d As New System.DateTime


The reason you need the cast is because while the thing being stored within the reader is actually a DateTime object, readers return their data as type Object. Everything in .NET can be pointed to by a variable defined as Object--even DateTime's.

e.g.

Dim o As Object = New DateTime()

When you get back the item from the reader, it appears to be an Object, and in order to treat it as a DateTime, you perform the cast.

You can easily confirm this by evaluating the return type of the Item call you are performing:

Screenshot
0
 

Author Comment

by:mpdillon
ID: 38810000
Now I understand. Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why, and when, to use Windows Workflow 1 31
Help with sorting data in Listbox using VB.NET 3 53
asp.net mvc input file 2 32
Syntax Error 2 44
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

17 Experts available now in Live!

Get 1:1 Help Now