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,
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;
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
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.
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 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
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
- Solution of error converting data type varchar to datetime in SQL Server
- Get the current date in Python
- SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
- nested exception is org.hibernate.PropertyAccessException: Null value was assigned to a property
- [JS] use date. Now(). Tostring() to generate serial number
- The COMMIT TRANSACTION request has no corresponding BEGIN
- Error attempting to get column time from result set. Cause: java.sql.SQLFe
- Simple understanding and basic operation of mongodb
- Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column xxxx
- MySQL partitions the existing tables of the data table
- “Invalid month” in SQL query
- The attribute error: he has no attributes.
- Shell gets the current time of the system and formats it
- Error converting data type nvarchar to datetime
- pandas parse_ Data exception, automatically skip
- The shell gets the current time of the system and formats it
- Converting string object into datetime type in pandas
- Abnormal reading after stm32f4 RTC time setting
- 12-web security — error injection based on SQL Server — and, convert, cast
- Several calculation methods of Python execution time