Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date conversion using .ToString vs Format

Posted on 2013-01-21
13
Medium Priority
?
263 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview

971 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