Date format convert problem for SQL server

[eotret]

Hello everyone,

In an application I wrote, I need to change the date information entered by the user in the format DD/MM/YYYY to YYYY/MM/DD in order to write it to the SQL database field.

I am using the following code structure for this change, and I get the correct output as YYYY/MM/DD.

However, when it comes to writing this data to SQL, I receive the following error message.

I am using 'date' or 'datetime' as the data type in the database, but it doesn't make a difference.

Does anyone have any ideas on this issue?

ERROR MESSAGES:

Error: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type nvarchar to numeric. (8114) (SQLExecDirectW)')


CODE:

from datetime import datetime, date

date = request.form['DATE']
print(date)
date_str = datetime.strptime(date, '%d/%m/%Y')
date_sql_format = datetime.strptime(date_str.strftime('%Y-%m-%d'), '%Y-%m-%d').date()
print(date_sql_format)
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()
cursor.execute('''INSERT INTO dbo.Users (DATE) VALUES (?) ''', (date_sql_format))
conn.commit()
cursor.close()
conn.close()

date                 = 05/08/2024
date_sql_format = 2024-08-05

[Larz60+]

If changing an existing row in a table, you need to use 'UPDATE', not 'INSERT'

[eotret]

If changing an existing row in a table, you need to use 'UPDATE', not 'INSERT'

We are not updating an existing record, we are trying to enter a new date. Therefore, we are using INSERT.

[deanhystad]

Please post output and the entire error message, including any traceback.

According to the documentation, parameters is a sequence. I think you meant (date_sql_format) to be a tuple, but it is just parenthesis around a value. When making a tuple that contains a single value, use a trailing comma, (date_sql_format,).

Because you are not passing a tuple, the execute command uses date_sql_format as the parameters. Instead of adding "2024-08-05" I think it is trying to add ("2", "0", "2", "4", "-", "0".....) you get the idea.

Try this:

cursor.execute('''INSERT INTO dbo.Users (DATE) VALUES (?) ''', (date_sql_format,))

[eotret]

Please post output and the entire error message, including any traceback.

According to the documentation, parameters is a sequence. I think you meant (date_sql_format) to be a tuple, but it is just parenthesis around a value. When making a tuple that contains a single value, use a trailing comma, (date_sql_format,).

Because you are not passing a tuple, the execute command uses date_sql_format as the parameters. Instead of adding "2024-08-05" I think it is trying to add ("2", "0", "2", "4", "-", "0".....) you get the idea.

Try this:

cursor.execute('''INSERT INTO dbo.Users (DATE) VALUES (?) ''', (date_sql_format,))

Thank you for the reply.

Since there are many parameters within the application, I wanted to include the problematic area.

Below is the code related to the database connection and the user input sections, as well as the error message.

Among these parameters, the lines below specifically convert the date information into a format suitable for the SQL server.

All data, except for the date information, is being successfully recorded.

gorusme_tarihi = request.form['Gorusme_Tarihi']
print(gorusme_tarihi)
gorusme_tarihi_str = datetime.strptime(gorusme_tarihi, '%d/%m/%Y')
gorusme_tarihi_sql_format = datetime.strptime(gorusme_tarihi_str.strftime('%Y-%m-%d'), '%Y-%m-%d').date()
print(gorusme_tarihi_sql_format)

app = Flask(__name__)
app.secret_key = 'secretkey' 
Bootstrap(app)

# Database bconnection
server = '192.168.1.1'
database = 'TESTDB01'
username = 'api'
password = 'Password'
driver = '{ODBC Driver 17 for SQL Server}'
connection_string = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}'


@app.route('/')
def index():
    return render_template('index.html')


@app.route('/kayit', methods=['POST'])
def kayit():
    try:
        donem = request.form['Donem']
        akis_no = request.form['Akis_No']
        gsm = request.form['GSM']
        name = request.form['Name']
        mail = request.form['Mail']
        birim = request.form['Birim']
        bolum = request.form['Bolum']
        departman = request.form['Departman']
        pozisyon = request.form['Pozisyon']
        unvan = request.form['Unvan']
        jci = request.form['JCI']
        bu_head = request.form['BU_Head']
        yonetici = request.form['Yonetici']
        hrbp = request.form['HRBP']
        calisma_lokasyonu = request.form['Calisma_Lokasyonu']
        proje = request.form['Proje']
        cv_kaynagi = request.form['CV_Kaynagi']
        aday_durumu = request.form['Aday_Durumu']
        gorusme_tarihi = request.form['Gorusme_Tarihi']
        print(gorusme_tarihi)
        gorusme_tarihi_str = datetime.strptime(gorusme_tarihi, '%d/%m/%Y')
        gorusme_tarihi_sql_format = datetime.strptime(gorusme_tarihi_str.strftime('%Y-%m-%d'), '%Y-%m-%d').date()
        print(gorusme_tarihi_sql_format)
        conn = pyodbc.connect(connection_string)
        cursor = conn.cursor()
        cursor.execute('''                       
            INSERT INTO dbo.Users (Donem, Akis_No, GSM, Name, Mail, Birim, Bolum, Departman, Pozisyon, Unvan, JCI, Bu_Head, Yonetici, HRBP, Calisma_Lokasyonu, Proje, CV_Kaynagi, Aday_Durumu, Gorusme_Tarihi)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (donem, akis_no, gsm, name, mail, birim, bolum, departman, pozisyon, unvan, jci, bu_head, yonetici, hrbp, calisma_lokasyonu, proje, cv_kaynagi, aday_durumu, gorusme_tarihi_sql_format))
        conn.commit()
        cursor.close()
        conn.close()

        flash('Kaydiniz Gerçekleşti', 'success')

    except Exception as e:
        flash('Kaydiniz gerçekleşmedi, tekrar deneyin', 'danger')
        print(f"Error: {e}")
    return redirect(url_for('index'))

10.251.3.11 - - [09/Aug/2024 08:56:15] "GET / HTTP/1.1" 200 -
10.251.3.11 - - [09/Aug/2024 08:56:17] "GET /favicon.ico HTTP/1.1" 404 -
05/08/2024
2024-08-05
Error: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type nvarchar to numeric. (8114) (SQLExecDirectW)')
10.251.3.11 - - [09/Aug/2024 08:56:23] "POST /kayit HTTP/1.1" 302 -
10.251.3.11 - - [09/Aug/2024 08:56:23] "GET / HTTP/1.1" 200 -

[Pedroski55]

No need to change the date around, MySQL can accept any format, as long as you advise it about the format:

Just use STR_TO_DATE('05/08/2024', '%d/%m/%Y')

INSERT INTO babydb.sales_agro_products (customer_id, product_name, product_class, product_price_kG, amount_ordered, sales_value, Date) VALUES ('Big John', 'Kill Everything', 'product_class', 112.34, 678, 76166.52, STR_TO_DATE('05/08/2024', '%d/%m/%Y'))