Solved

Date conversion using .ToString vs Format

Posted on 2013-01-21
13
257 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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