SQL查詢月、天、周、年(MySql的例項對比)
1
2/**//*計算今天是星期幾*/
3select datename(weekday,getdate())
4
5/**//*查詢本年的資料*/
6select * from users where year(time)=year(getdate())
7
8/**//*查詢本月的資料,time是表users中代表時間的欄位*/
9select * from users where month(time)=month(getdate()) and year(time)=year(getdate())
10
11/**//*查詢今天的資料,time 是表中代表時間的欄位*/
12select * from users where day(time)=day(getdate()) and month(time)=month(getdate()) and year(time)=year(getdate())
13
14
15/**//*計算那一天是星期一*/
16SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
17
18/**//*計算那一天是週末*/
19select dateadd(wk,datediff(wk,0,getdate()),6)
20
21/**//*查詢本週的資料*/
22select * from users where DATEPART(wk, time) = DATEPART(wk, GETDATE()) and DATEPART(yy, time) = DATEPART(yy, GETDATE())
23
24
25/**//*查詢本日的記錄*/
26select * from users where (DATEDIFF(dd, time, GETDATE()) = 0)
27
28/**//*查詢本月的記錄*/
29select * from users where (DATEDIFF(mm, time, GETDATE()) = 0)
30
31/**//*查詢本年的記錄*/
32select * from users where (DATEDIFF(yy, time, GETDATE()) = 0)在MySql中實現:
1 1——
2 本年:
3 select * from loanInfo where year(date)=year(getdate())
4
5 2——
6 本月:
7 select * from loanInfo where year(date)=year(getDate()) And month(date)=month(getdate())
8
9 3——
10 本日:
11 select * from loanInfo where year(date)=year(getDate()) And month(date)=month(getdate()) and Day(date)=Day(getDate())
12
13
14
15SELECT * FROM table WHERE (MONTH(欄位) = MONTH(GETDATE()))
2/**//*計算今天是星期幾*/
3select datename(weekday,getdate())
4
5/**//*查詢本年的資料*/
6select * from users where year(time)=year(getdate())
7
8/**//*查詢本月的資料,time是表users中代表時間的欄位*/
9select * from users where month(time)=month(getdate()) and year(time)=year(getdate())
10
11/**//*查詢今天的資料,time 是表中代表時間的欄位*/
12select * from users where day(time)=day(getdate()) and month(time)=month(getdate()) and year(time)=year(getdate())
13
14
15/**//*計算那一天是星期一*/
16SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
17
18/**//*計算那一天是週末*/
19select dateadd(wk,datediff(wk,0,getdate()),6)
20
21/**//*查詢本週的資料*/
22select * from users where DATEPART(wk, time) = DATEPART(wk, GETDATE()) and DATEPART(yy, time) = DATEPART(yy, GETDATE())
23
24
25/**//*查詢本日的記錄*/
26select * from users where (DATEDIFF(dd, time, GETDATE()) = 0)
27
28/**//*查詢本月的記錄*/
29select * from users where (DATEDIFF(mm, time, GETDATE()) = 0)
30
31/**//*查詢本年的記錄*/
32select * from users where (DATEDIFF(yy, time, GETDATE()) = 0)在MySql中實現:
1 1——
2 本年:
3 select * from loanInfo where year(date)=year(getdate())
4
5 2——
6 本月:
7 select * from loanInfo where year(date)=year(getDate()) And month(date)=month(getdate())
8
9 3——
10 本日:
11 select * from loanInfo where year(date)=year(getDate()) And month(date)=month(getdate()) and Day(date)=Day(getDate())
12
13
14
15SELECT * FROM table WHERE (MONTH(欄位) = MONTH(GETDATE()))
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7299296/viewspace-555683/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- vue例項+axios-天氣查詢VueiOS
- 【Mysql】 mysql 按年度、季度、月度、周、日SQL統計查詢MySql
- MySQL和MongoDB設計例項對比MySqlMongoDB
- sql 連線查詢例項(left join)三表連線查詢SQL
- SQL查詢連續號碼段的解法(例項)SQL
- mysql按年度、季度、月度、周、日統計查詢的sql語句MySql
- Oracle/MySQL/PostgreSQL 簡單查詢的效能對比OracleMySql
- MySQL與MongoDB設計例項對比QYMySqlMongoDB
- 使用SSMS連線和查詢 SQL Server 例項SSMSQLServer
- python連結mysql查詢資料例項PythonMySql
- oracle 例項表查詢Oracle
- ORACLE 當月、本週、當年SQL查詢OracleSQL
- 對比SQL中簡單巢狀查詢與非巢狀查詢CFSQL巢狀
- 一條SQL完成跨資料庫例項Join查詢SQL資料庫
- flask查詢whois資訊例項Flask
- Mysql中DATE_SUB 使用方法結合查詢一天內,一週內,一月內的資訊例項講解MySql
- Mongodb操作之查詢(循序漸進對比SQL語句)MongoDBSQL
- 探究MySQL中SQL查詢的成本MySql
- MySQL按分/時/天/周/月/季度/半年/年分組MySql
- mysql timestamp比較查詢MySql
- mysql查詢今天,昨天,近7天,近30天,本月,上一月資料的方法MySql
- MySQL中使用or、in與union all在查詢命令下的效率對比MySql
- 查詢oracle比較慢的session和sqlOracleSessionSQL
- mysql查詢效率慢的SQL語句MySql
- MySQL 查詢處理 SQL查詢執行順序MySql
- Python查詢MySQL進行遠端採集圖片例項PythonMySql
- 例項講解MySQL資料庫的查詢最佳化技術(轉)MySql資料庫
- 快遞鳥查詢訂單例項單例
- MySQL管理之SQL語句例項MySql
- MySQL 查詢大表注意事項MySql
- Mysql大表查詢注意事項MySql
- 查詢oracle比較慢的session和SQL[轉]OracleSessionSQL
- SQL查詢的:子查詢和多表查詢SQL
- 十七、Mysql之SQL優化查詢MySql優化
- mysql 查詢建表語句sqlMySql
- c++map 查詢元素和list查詢元素速度對比C++
- 在mysql查詢效率慢的SQL語句MySql
- MySQL:查詢欄位數量多少對查詢效率的影響MySql