Question 353175 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to convert a German date in Excel to an SQL date string for an INSERT statement?

I am working with an Excel spreadsheet that contains dates in the German format DD.MM.YYYY, and I’m trying to craft a formula that automatically generates an SQL insert statement for each row. I’m running into issues converting the German date to the SQL-expected format YYYY-MM-DD.

Here’s the formula I’m using:

="INSERT INTO Bestellungen_1NF (BestellNr, Bestelldatum, KundenNr, KundenVorname, KundenNachname, Bestellposition, ArtikelNr, Preis, Menge) VALUES (" & A2 & "; '" & TEXT(B2;"yyyy-mm-dd") & "'; " & C2 & "; '" & D2 & "'; '" & E2 & "'; " & F2 & "; " & G2 & "; " & H2 & "; " & I2 & ");"

The issue is that the date is output as yyyy-00-dd instead of the actual date 2023-04-05. Here is an example of the generated SQL statement that isn’t correct:

INSERT INTO Bestellungen_1NF (BestellNr, Bestelldatum, KundenNr, KundenVorname, KundenNachname, Bestellposition, ArtikelNr, Preis, Menge) VALUES (1; 'yyyy-00-dd'; 00520; 'Lisa'; 'Grub'; 1; 55220; 100; 3);

However, I need the following correct SQL statement:

INSERT INTO Bestellungen_1NF (BestellNr, Bestelldatum, KundenNr, KundenVorname, KundenNachname, Bestellposition, ArtikelNr, Preis, Menge) VALUES (1; '2023-04-05'; 00520; 'Lisa'; 'Grub'; 1; 55220; 100; 3);

How can I adjust the Excel formula to get the correct date in SQL format?

Tags:

2

Answers


  1. 0

    Try this once-

    "INSERT INTO Bestellungen_1NF (BestellNr, Bestelldatum, KundenNr, KundenVorname, KundenNachname, Bestellposition, ArtikelNr, Preis, Menge) 
VALUES (" & A2 & "; '" & MID(B2, 7, 4) & "-" & MID(B2, 4, 2) & "-" & MID(B2, 1, 2) & "'; " & C2 & "; '" & D2 & "'; '" & E2 & "'; " & F2 & "; " & G2 & "; " & H2 & "; " & I2 & ");"

    The MID function is used to extract the year, month, and day components from the German date in cell B2 and rearrange them in the SQL format.

    Login or Signup to reply.
  2. 0

    You are on the right way.You can figure out the built in Excel function TEXT to do this job.

    But please aware: This function use your locale settings. I assume, you are on a german based Excel. If so, you can convert the date as follows:

    German date (Example):

    Assumed, the date is in Column A1, B1 […] you can use the following formula to convert the date to MySQL Format:

    =TEXT(A1;"JJJJ-MM-tt")
=TEXT(B1;"JJJJ-MM-tt")

    The format string means for German locale(!):

    • JJJJ = Year in four digits
    • MM = Month with leading zero
    • tt = Day with leading zero

    Excel-Text function — German locale

    Excel-Text function — US locale

    Your code works like a charm, after a small modification, as described above.

    One of the oldest excel annoyingness. The locale settings a relevant in native code. I think, many years ago, (source)code must also be translated, to break new ground.

    Excel SQL Code

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search