SQL Server database table import SqlLite database table to maintain the format of the datetime Field

In the process of writing the query function, a problem occurs: query by date range. The SQL statement is: wheredt, the user selects the start date, dt, and the user selects the end date. Data 1 in the database. The start time of the test data I selected is and the end date is 2009-06-01. The expected result should be the first data record. No SQL statement is executed.

In the process of writing the query function, a problem occurs: query by date range. The SQL statement is: where dt = the user selects the start date dt = the user selects the end date. Data 1 in the database. The start time of the test data I selected is and the end date is 2009-06-01. The expected result should be the first data record. No SQL statement is executed.

In the process of writing the query function, a problem occurs: query by date range. The SQL statement is: where dt> = the user selects the start date & dt <= the user selects the end date. Data 1 in the database. The start time of the test data I selected is and the end date is 2009-06-01. The expected result should be the first data record. No error occurred while executing the SQL statement, but the query result is null. Search for the reason on the Internet, so that you can know that the string time comparison is based on a digit. Only strings corresponding to the format can be compared. The time format selected by the user is YYYY-MM-dd, and the database data format is YYYY-M-d. An error occurred while comparing the date to the hour.

The solution is to change the data format in the database to YYYY-MM-dd. The problem arises.

The database used by the user is Sqlserver. Because of development requirements, our company uses Sqlite database and needs to import the user's Sqlserver data table to our Sqlite database. In SqlServer database, the time format is YYYY-MM-dd HH: mm: ss (SqlServer database automatically standardizes the inserted datetime type data into YYYY-MM-dd HH: mm: ss type) When importing, I chose the Navicat Premium tool. The data is imported successfully. When you open the data table in Sqlite, the data in the table is in the YYYY-M-d format. The reason is that the Sqlite database does not support data of the datetime type. Therefore, dt in the database is of the Text type and cannot automatically regulate the Date Field Format as Sqlite does. 1: In Sqlite, the data is, and no 0 is supplemented. the end date of the test data is, and the comparison time is 6> 0. Therefore, the first data in the database is not queried.

Solution: import data from sqlserverto excel, and then import .xls to the Sqlite database.

1. import data from SqlServer to Excel. Before importing, create an Excel document and write the field name in the database to the first row as the column header (that is, this document is used as the data import template) 2. The data export function provided by SqlServer is used for export. The specific method is not described in detail. When selecting a target, select the created Excel file in the Excel file path. The first row contains three optional column names.

NOTE: If an Excel template is created without writing the column header before export, an error 4 is returned. If the exported date and time are still not 0, try to create an Excel template, right-click the date column, set the cell format to text, and then repeat the preceding steps.

2. Finally, import the exported Excel file to the Sqlite database. You can use the Navicat Premium tool to import the exported Excel file. You can select whether the delimiter of the date and time data is-or /. The specific import method is not described.