SQL Server queries problems by time period, SQL Server queries

SQL Server queries problems by time period, SQL Server queries

Baidu's materials are saved as follows:

When writing SQL statements that query by time period, we generally write the query conditions as follows:

where date>='2010-01-01' and date<='2010-10-1'

However, some SQL statements are converted into the following:

　　where date>='2010-01-01 0:00:00' and date<='2010-10-1:0:00:00'If you look at this condition again, you may understand it,

That is, the data after '2017-10-1 0:00:00 'cannot be found, for example ('2017-10-1: 08: 25: 00'), that is, the data of cannot be found.

If you know the reason, you can modify the query condition:

Where date> = '2014-01-01 'and date <= '2014-10-1 23:59:59' or where date> = '2014-01-01 'and date <= '2014-10-2'

A field in a table is of the Datetime type stored as "YYYY-MM-DD 00:00:00"

(1), such as data

21:22:22
19:21:11
23:10:22

(2) Useselect * from TABLE where date between '2009-1-22' And '2009-1-22' To query records with a date of, the results cannot be found.

(3) Cause

The default Time of the short date type is 00:00:00. Therefore, when between is used as the restriction condition, it is equivalent to between '2017-1-22 00:00:00 'and '2017-1-22 00:00:00 ', therefore, no data can be found.

(4) Solutions

-- Solution 1: convert the date format of fields in the database select * from tb where convert (varchar (10), riqi, 120) = '2017-01-22 '-- solution 2: select * from tb where riqi between '2017-01-22 00:00:00 'and '2017-01-22 23:59:59' -- result/** // * id riqi ---- hour ---------------------------------------------------------- A 21:22:22. 000 B 19:21:11. 000 C 23:10:22. 000 (3 rows affected )*/

Summary

The above is a time period query issue of SqlServer introduced by xiaobian. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!