0

I'm trying to insert date and time to SQL server in Linux (raspbian) environment using python language.So far i was able connect to MS Sql and also i created a table and im using pyodbc.

#! /user/bin/env python import pyodbc import datetime dsn = 'nicedcn' user = myid password = mypass database = myDB con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database) cnxn = pyodbc.connect(con_string) cursor = cnxn.cursor() string = "CREATE TABLE Database3([row name] varchar(20), [my date] date), [my time] time)" cursor.execute(string) cnxn.commit()

This part complied without any error.That means i have successfully created a table right? Or is there any issue?

I try to add date and time this way.

 now = datetime.datetime.now() d1 = now.date() t2 = now.strftime("%H-%M-%S") cursor.execute("insert into Database3([row name], [my date], [my time]) values (?,?,?)", ('new1', d1, t2)) cnxn.commit()

But i get this error. pyodbc.ProgrammingError:

('HY004', '[HY004] [FreeTDS] [SQL Server]Invalid data type (O) (SQLBindParameter)')

help me please. thanks in advance

Improve this question
10
  • I'm not overly familiar with python but have you tried with colons instead of dashes for the time "%H:%M:%S"? Commented Jun 30, 2014 at 14:24
  • What version of the SQL Server native client are you using? Commented Jun 30, 2014 at 14:26
  • I'm Using MS SQL 2008 R2 Express Commented Jun 30, 2014 at 14:37
  • "%H:%M:%S" yes i tried this but i get the same result Commented Jun 30, 2014 at 14:38
  • @Kevin777 2008 R2 Express is the version of SQL Server you are using, not the version of the ODBC driver. What version of the ODBC driver for SQL Server is used in your DSN? This can be found using the ODBC Data Source Administrator. Commented Jun 30, 2014 at 14:42

1 Answer 1

Reset to default
3

If you are on Windows, install the latest version of the Microsoft ODBC Driver for SQL Server to ensure the DATE and TIME types are supported.

If you are on Linux or macOS, use this page to determine the latest driver available for your distribution.

Use parameter placeholders and pass the values as date and time objects for the current datetime value.

now = datetime.datetime.now() sql = "insert into Database3([row name], [my date], [my time]) values (?,?,?)" cursor.execute(sql, ('new1', now.date(), now.time())) cnxn.commit()

Note that the above code was only tested on Windows.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I'm on Linux (raspberry pi)
@Kevin777 Updated with link to documentation for Microsoft ODBC Driver for SQL Server on Linux
if there is any issue with my data types ??
date and time datatypes were introduced in SQL Server 2008. Upgrade FreeTDS to the latest version, or use the Microsoft ODBC Driver for SQL Server on Linux.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.