Import date format from csv

Hello I am trying to import csv files where the date format is DDMMYYYY (eg: 16012020) and export it in SQLDATE.
I thought there was a way to set the date format when importing it …

dat = DateTime.FromString(csv.GetCell(row,6),"DDMMYYYY") g = dat.SQLDate.ToText

But then I saw one is meant to set a locale, but I could not find a locale for this format .
Where am I going wrong here?

Markus

This is not a date format.

A date format uses a field delimiter (usually “-”, without quotes).

Read:
https://en.wikipedia.org/wiki/ISO_8601

Now, if you have tons of these values, you can create a Method (Function ?) that check what the fields are (read the first 2 characters and if there are values up to 12, it is a day number, and so on for the two next and the four next) and return the real date with delimiters in a String or Text or.

example of locale
Locale

If you put DDMMYYYY in a String, you can use .Right, .Middle and .Left to form YYYY-MM-DD and use that with DateTime.FromString

Emile,

this is the format the German tax authorities orders you to use according to the DATEV standards… I understand, that it is not an ISO Code format and don’t need to check what the fields are, since the format is given. If there is no way of defining the format, i will use Alberto´s workaround. It just seems to be odd that Xojo does not offer anything in the way date formatting that is non standard…

Xojo provide conversion from standard (02-03-2020) to set in a Date Class.

Now you said the numbers always comes this way (never different than MMDDYYYY)… the use of Left, MLid and Right to determine the fields and build a Date comes:

VAR myDate As New Date

myDate.Day = Left(“02032020”, 2)
myDate.Month = Mid(“02032020”, 2, 2)
myDate.Year = Right(“02032020”, 4)

myDate now holds 02032020 as 02-03-2020.

Check the syntas as I wrote that from memory.

Here is the code working, seems a bit clumsy.but it does the trick:

Var g As String Var d,m,y As Integer g = trim(csv.GetCell(row,6)) // just in case there is any space on either side d = val(Left(g, 2)) m = val(Mid(g, 3, 2)) // thought it should be 2,2 , but 3,2 turned out to be correct y = val(Right(g, 4)) Var dat As new DateTime(y,m,d)

And then I insert it as dat.SQLDate into the database. Quite a few steps. hope that is the best way.
Thanks for your help.

[quote=477748:@Markus Bolder]m = val(Mid(g, 3, 2)) // thought it should be 2,2 , but 3,2 turned out to be correct
[/quote]
It would be 2,2 if you used middle() instead of mid().

@Markus Bolder I see that you are using ‘Var’ and ‘DateTime’, my recommendation is to use API 2.0 for the rest of the code too. It will be better on the long run.

Something like this:

Var g As String Var d,m,y As Integer 'g = trim(csv.GetCell(row,6)) // just in case there is any space on either side g = csv.CellValueAt(0, 0).Trim // used 0,0 just for my test 'd = val(Left(g, 2)) d = g.left(2).Val 'm = val(Mid(g, 3, 2)) // thought it should be 2,2 , but 3,2 turned out to be correct m = g.Middle(2,2).Val 'y = val(Right(g, 4)) y = g.Right(4).Val Var dat As new DateTime(y,m,d)

Also, if you don’t need the d, m, y and you are using them just to get ‘dat’ you can do something like this:

Var g As String = csv.CellValueAt(0, 0).Trim var dat as DateTime = DateTime.FromString(g.Right(4) + "-" + g.Middle(2,2) + "-" + g.Left(2))