After connecting to the SqlServer database with pymssql, when inserting a block of data containing the time field into the database, the Conversion error is reported as follows:
‘Conversion failed when converting date and/or time from character string. Db-lib error message 241,
Process:
1, directly in the database to execute the insert statement
INSERT INTO [dbo].[ClustedResult]([outputScript],[classId],[startTime],[endTime]) VALUES(0x76A914E67550CD61C6D89DF67F4D5F8E0B4AD30013C65888AC,'493886f0-7387-11e7-ab02-047d7ba2a507','2017-07-28 19:24:14.431000','2017-07-28 19:24:14.431000')
An error was also reported, indicating that string and date conversion failed.
2, view the data type
of each field in the table: [startTime],[endTime] data type of two fields is datetime
There are 6 data types of sqlserver 2008 representing time on the Internet.
time, date, smalldatetime, datetime, datetime2, datetimeoffset;
Datetime
date and time section, can represent date range from January 1, 1753 AD 00:00:00 000 to December 31, 9999 23:59:59.997, accurate to 3.33 ms, it requires 8 bytes of storage space. The DateTime field type corresponds to the time format of YYyy-Mm-DD HH: MM: Ss.FFF, three F’s, accurate to 1 ms (ms), example 2014-12-03 17:06:15.433
Smalldatetime
date and time data from January 1, 1900 to June 6, 2079 are accurate to minutes. Smalldatetime values of 29.998 seconds or less are rounded down to the closest minute, and SmallDatetime values of 29.999 seconds or more are rounded up to the closest minute. 4 bytes of storage space is required.
Date
SQL Server 2008 newly introduced data type. It represents a date, without a time component, and can represent dates ranging from January 1, A.D., to December 31, 9999. Only three bytes of storage is required.
DateTime2
DateTime2 field type corresponding to the time format is yyyy-mm-dd HH: MM :ss.fffffff, 7 f, accurate to 0.1 microsecond (s), example 2014-12-03 17:23:19.2880929.
The data is read in the same format regardless of whether your field is datetime or SmallDatetime (e.g. 1900-01-01). A datetime doesn’t show its milliseconds, but it does show up in milliseconds during time comparisons, making the time periods not equal.
If it’s SQL Server 2005, use Smalldatetime to get half of the data, even if it doesn’t look any different when queried.
if you are SQL Server 2008, please use date. Although 3 bytes is not much different from 4 bytes, it has greatly improved in design and logical clarity.
If the SQL date function is used for assignment, the DateTime field type is GETDATE() and the DateTime2 field type is SYSDATETIME().
There’s a very detailed reference: http://www.csdn.net/article/1970-01-01/282777
http://blog.csdn.net/justdb/article/details/7575021
3. After a series of knowledge catch up with, look at the characteristics of time types in python
begin = datetime.datetime.now()
print type(begin)
print str(begin)
#运行结果：
<type 'datetime.datetime'>
2017-07-28 19:24:14.431000
4. The reason is that python and sqlserver have different time types: format. Offer two solutions:
Solution 1: Unify the Python Datetime format with the database format.
timeStamp = STR (datetime. Datetime. Now ())[0:-3] scheme 2: modify the database data type to a wider range of datetime2
