Date conversion using .ToString vs Format

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
mpdillonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
käµfm³d 👽Commented:
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
 
mpdillonAuthor Commented:
System.runtimeType: ("DateTime" FullName = "System.DateTime")

Does that help?
0
 
käµfm³d 👽Commented:
What is the entire line that errors?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
UnifiedISCommented:
Why can't you do this:

If CoReader.IsDBNull(7) Then ProducedDtString = "null" Else ProducedDtString = format(CoReader.Item("ProducedDt"), "MM/dd/yyyy")
0
 
mpdillonAuthor Commented:
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
 
käµfm³d 👽Commented:
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
 
mpdillonAuthor Commented:
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
 
käµfm³d 👽Commented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
UnifiedISCommented:
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
 
mpdillonAuthor Commented:
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
 
käµfm³d 👽Commented:
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
 
mpdillonAuthor Commented:
Now I understand. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.