Convert Date with format

I am reading an Oracle database and writing code in . There is an Oracle varchar filed which represents date . I am looping thru rows
Some dates are in YYYY/MM/DD and some are in DD/MM/YYYY format .  How can my VB routine read this stirng and have the value converted into "YYYY/MM/DD" format ( in  a string variable )

sample  oracle values   10/12/2010   13/03/2011  2015/01/05   ( In 3 different rows in oracle)
As I loop thru rows I need to get the values (one at a time)   2010/12/10   2011/03/13  2015/01/05     in the string variable

(C# code is also fine, But VB.NET is desired)
Sam OZAsked:
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.

AndyAinscowFreelance programmer / ConsultantCommented:
First point:  Whoever designed your database is a ******.  NEVER store dates as strings.

If the first / character in the string is at the fifth position then you know the string starts with the year.
Concerning the possibilities:  10/12/2010   13/03/2011
The second possibility is clear but the first can be 10th of December or 12th of October.  You have NO WAY to say which it is based just on that string unless you can be 100.00000% certain which format was used.
Nitin SontakkeDeveloperCommented:
Code is very simple, which I can give you in minutes. Bigger question is when the year appears last, see first two values given by you, and when values can be swapped meaning in second example value, instead of 13 you have 05 - 05/03/2011, how are you determining whether it is 5th of March or 3rd of May.

Or you are certain of their positions?
Sam OZAuthor Commented:
There are only YYYY/MM/DD  and  DD/MM/YYYY   format values
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

AndyAinscowFreelance programmer / ConsultantCommented:
Bear in mind if you are wrong then you will get garbage

        Dim sar As String() = "10/11/2012".Split("/")
        Dim sTrgt As String
        If sar(0).Length = 2 Then  ' day month year
            sTrgt = sar(2) + "/" + sar(1) + "/" + sar(0)
        Else    'year month day
            sTrgt = sar(0) + "/" + sar(1) + "/" + sar(2)
        End If

Experts Exchange Solution brought to you by

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
Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
>>sample  oracle values   10/12/2010   13/03/2011  2015/01/05   ( In 3 different rows in oracle)
As I loop thru rows I need to get the values (one at a time)   2010/12/10   2011/03/13  2015/01/05     in the string variable 

how you determine "10/12/2010" is 10 Dec 2010 and it's not 12 Oct 2010?

I would suggest you to do a data patching to have a single date format for your stored dates. and as Andy already mentioned, store dates as date instead of chars, this wil give you much for data accuracy.
NEVER store dates as strings.

the YYYYMMDD format (with or without separators) is not so bad, you even can calculate date differences by using function like STR_TO_DATE in oracle. for your database you should either convert all string dates to DATE fields, or, at least convert all DD/MM/YYYY strings to YYYY/MM/DD what easily could be done with substring concatenation or str-to-date and date-to-string functions as already suggested.

Nitin SontakkeDeveloperCommented:

      String[] dateParts = "2015/01/05".Split('/');
      DateTime dateDerived = new DateTime();
      Int32 intDay, intMonth, intYear;
      if (dateParts[2].Length == 4)  // dd/mm/yyyy
        intDay = Int32.Parse(dateParts[0]);
        intMonth = Int32.Parse(dateParts[1]);
        intYear = Int32.Parse(dateParts[2]);
        intDay = Int32.Parse(dateParts[2]);
        intMonth = Int32.Parse(dateParts[1]);
        intYear = Int32.Parse(dateParts[0]);
      dateDerived = new DateTime(intYear, intMonth, intDay); // Apply ToString() as desired afterwards. All invalid dates will be thrown as expections which you can catch

Open in new window

storing dates in strings has some advantages: you easily can handle non-mandatory fields by letting the string empty, or convert them to numbers, or sort columns in grids. also exchange to other databases or apps is much simpler.

Nitin SontakkeDeveloperCommented:
Apologies for jumping in but here are my thoughts....

Dates should be stored as dates only in database. For non-mandatory fields having null is rather appropriate option than storing empty string. This applies even for the non-mandatory string (char, varchar, etc.) columns too.

Representation of dates outside of the database is a separate issue and must be dealt with separately. Information must be stored in it's most raw format in the database and that's what the database is for and best at.

All data transport from db to outside db mostly happens in string format only. However, the date should be transformed in their desirable display (or any other) format nearest to it's consumption end-point.

Any other usage of the information is job of middle layer / presentation layer. Database should never assume any responsibility for the requirements of other layers.

With world coming closure and applications reach increasing, I often tend to go far as to insisting on storing all dates in UTC format only.

Strictly my personal opinion, of course!
AndyAinscowFreelance programmer / ConsultantCommented:
Storing null is an easy way to handle a non required value.  Filtering, sorting and indexing on date fields is trivial, more complex (slower) for strings.  The reason is because a date is actually a numerical value when stored.
Storing null is an easy way to handle a non required value

it is for storing. it is not for retrieving data or handle different output formats or getting year, month, day from date.

Filtering, sorting and indexing on date fields is trivial
sorry, but it is not. if you put a table output into excel or any other non-database application, it doesn't know that the column is a date, and you would have to a lot of additional steps to get it imported, if at all. it is a good chance that it doesn't work.

more complex (slower) for strings.
that isn't true for date strings yyyymmdd. you will not get any measurable differences.

note, i used date fields in access, db2, mysql, sqlserver, oracle, and more. each of them handles dates and times differently. some have only one internal timestamp format from year to nanoseconds for ALL date/time formats, what can't be a reasonable storage for a simple date. it is always a mess if you have to fight against defaults which come along with date/time handling in databases. if you store dates as numbers or decimals, you either have to store (and handle nulls) everywhere or you have to handle (and suppress) a 0 date in every output. believe me, it is much easier to have a string date with 8 digits if you are outside of your database or if you have different data storage, or if you are dealing with csv and or xml which always are affine to strings (and nothing else).

AndyAinscowFreelance programmer / ConsultantCommented:
Note the question concerns storing dates in an oracle database, not a text file, not xml file, not excel....
the original question is
How can my VB routine read this stirng and have the value converted into "YYYY/MM/DD" format ( in  a string variable )

Andy, the dates are stored in strings ... and your solution also deals with strings ...

i never would recommend string dates if anyone is happy with the DATE format. from my experiences dates stored as strings in yyyymmdd can be handled without issues and sometimes with less compatibility issues.

AndyAinscowFreelance programmer / ConsultantCommented:
Yes Sara, my solution uses string manipulation.  Precisely because of the mess that can result in using a text field to store some data that would be more suitable to be stored as a date.
This question would not exist if the data had been stored in a field suitable.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.