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
- Local date time conversion in java8
- Mutual conversion between date and localdatetime
- How to get the current time in java time string
- Converting string object into datetime type in pandas
- Linux view current date and time
- When MATLAB uses audioread, an error is reported: Error using which Must be a string scalar or character vector.
- error: converting to execution character set: Illegal byte sequence
- “Pandoc document conversion failed with error 1033” error message appears in rstudio knit
- C – error: converting to execution character set：Illegal byte sequence
- Failed to convert value of type ‘java.lang.String‘ to required type ‘java.util.Date‘；
- Python conversion hex to string, high and low data processing
- Conversion between list and string array
- Vitis: platform out of date, makefile error at compile time; The modified application compiles to undefined reference
- 1、 Java 8 date and local date
- Compiler error message: cs1056: unexpected character handling
- Java String.split () special character processing
- String operation to delete the character at the specified position
- [Solved] Flowable Start Error: ClassCastException: java.time.LocalDateTime cannot be cast to java.lang.String
- Error message for HLS Video Fusion for the second time when using mars3d
- Solution of error converting data type varchar to datetime in SQL Server