Solved

Date conversion using .ToString vs Format

Posted on 2013-01-21
13
253 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 74

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 74

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 74

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 74

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 74

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
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…
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…
This video discusses moving either the default database or any database to a new volume.

708 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

18 Experts available now in Live!

Get 1:1 Help Now