fasted way to update a sql table

[timsteeman]

I have a table with the "wrong" sql dateformat.
Can anyone give me a clue to update all records (+10000) with a parameter?
When i do it my way, i get all dates to 00-00-0000

So; my dateformat is "DD-MM-YYYY" and i want to set it to "YYYY-MM-DD"

cmd.CommandText="update line set birthdate='"&ConvertToSQLDate (birthdate)&"'"
cmd.ExecuteNonQuery
End Sub

Private Sub ConvertToSQLDate( DDMMYYYY )
DateFormat( "dd-mm-yyyy" )
Ticks = DateParse( DDMMYYYY )
DateFormat( "yyyy-mm-dd" )
Return Date( Ticks )
End Sub

 

[Erel]

Start by converting a single date value. Can you post an example of the current format and the target format?

 

[timsteeman]

The code part

Hi Erel,
This is a part of the code. And a part of my db.
Thanks in advance for looking at my problem...
Tim

 

[Erel]

Can you post an example of one date?
I will then test the date conversion code.

 

[timsteeman]

And now with 1 record.

Thanks a lot for everything you're doing.
Attached the same database, now with 1 record.

The dateformat is 23-10-1933 and i want it to be converted to 1933-10-23
I understood that sqlite can only work with the last dateformat.

Tim

 

[Erel]

The problem is in this line:

cmd.CommandText="update famlid set gebdatum='"&ConvertToSQLDate (gebdatum)&"'"

ConvertToSQLDate is called with an unassigned variable named gebdatum. If you can the option Tools - Check for unassigned variables it will throw an error.

At the point of calling this sub the program doesn't know that it is part of a query.
You will need to change your code to go over all the rows and call update for each row with the calculated value (or implement this function inside the query with the SQL functions).