The datetime date in SqlServer is queried in mybatis and it is two days away

Problem Description:

The field type declared in the database is datetime, and the query time when the SQL statement is executed in SQL Server is normal, but the query through the project mybatis is 2 days later than the database time.


The first time I encountered this problem, it was a bit confusing. Use the following statement to query the current time on SQL Server:

select CONVERT(varchar(100), GETDATE(),20);

The time obtained is no problem:

What is the problem? I checked it online and the solution was basically like this:

Solution one
Converting the data type from date to datetime is not recommended, because after the table is designed, the table structure is generally not changed
Solution two (recommended)
When querying all data, perform a conversion on sql, convert (nvachar (100), field), so that the data found is no problem
Solution three
You can replace the JDBC version. I haven't tried this way. It is to replace the JDBC jar package, so the second way is recommended.

The first and third types are ruled out decisively. My database is datetime type, and then my JDBC jar is

relatively new, which is no problem.

Then use the second plan, and the result was an error. After careful investigation, I found that the nvachar in the convert (nvachar(100), field) is missing a letter. The exact wording is nvarchar. After changing to nvarchar, the output time format is not yyyy-MM- dd HH:mm:ss, it turns out that SQL Server datetime conversion string has a lot of articles, the following figure lists the domestic popular conversion:

So MyBatis converts datetime into yyyy-MM-dd HH:mm:ss, the correct way of writing is:

convert(nvarchar(100),CREATED_DATE,20) createdDate

What needs to be reminded here is that the createdDate in the above SQL corresponds to the corresponding attribute in the entity class. If createdDate is not added, there is no problem with the SQL syntax, and the time will be queried, but it will not respond to the front-end reception, so this is absolutely necessary Lack of.