sql查询当天记录 如何在SQL中按时间段查询数据
\u67e5\u8be2SQL\u4e2d\u65e5\u671f\u4e3a\u5f53\u5929\u7684\u8bb0\u5f55SELECT * FROM Table WHERE time= DATE_FORMAT(NOW(),'%Y-%m-%d')
\u4f7f\u7528date_format\u4e3b\u8981\u662f\u4e3a\u4e86\u683c\u5f0f\u5316\u65f6\u95f4\uff0c\u4f7f\u4e24\u8fb9\u7684\u65f6\u95f4\u683c\u5f0f\u4e00\u6837\uff0c\u6211\u5efa\u8bae\u8fd8\u662f\u683c\u5f0f\u5316\u4e00\u4e0b\u6bd4\u8f83\u597d\uff0c\u9632\u6b62\u51fa\u9519
sql server\uff1a
select * from \u8868 where \u53d1\u751f\u65e5\u671f>'2008-7-1' and \u53d1\u751f\u65e5\u671f<'2008-12-31'
access\uff1a
select * from \u8868 where \u53d1\u751f\u65e5\u671f>#2008-7-1# and \u53d1\u751f\u65e5\u671f<#2008-12-31#
\u8fd9\u6837\u5c31\u53ef\u4ee5\u4e86\uff0c\u6ce8\u610fsql server\u4e0eaccess\u4e2d\u7684\u65e5\u671f\u6709\u4e00\u70b9\u4e0d\u4e00\u6837\u3002
\u6269\u5c55\u8d44\u6599\uff1asql\u67e5\u8be2\u65e5\u671f\u8bed\u53e5
select * from ShopOrder where datediff(week,ordTime,getdate()-1)=0 //\u67e5\u8be2\u5f53\u5929\u65e5\u671f\u5728\u4e00\u5468\u5e74\u7684\u6570\u636e
select * from ShopOrder where datediff(day,ordTime,getdate()-1)=0 //\u67e5\u8be2\u5f53\u5929\u7684\u6240\u6709\u6570\u636e
SELECT * FROM A where datediff(d,datetime,getdate()) <=30 //\u524d30\u5929
SELECT * FROM A WHERE DATEDIFF(m, shijian, GETDATE()) <=1 // \u4e0a\u4e00\u6708
\u67e5\u8be2\u5f53\u5929\u8bb0\u5f55\u53e6\u7c7b\u7684\u65b9\u6cd5\uff1a
SELECT *
FROM j_GradeShop
WHERE (GAddTime BETWEEN CONVERT(datetime, LEFT(GETDATE(), 10) + ' 00:00:00.000')
AND CONVERT(datetime, LEFT(GETDATE(), 10) + ' 00:00:00.000') + 1)
ORDER BY GAddTime DESC
1、SQL在查询当天记录时要注意是从当天的0点0分0秒0毫秒开始，到次日0点0分0秒0毫秒截止，但不包含次日的0点0分0秒0毫秒。
2、注意：在不同数据库产品中，获得当天日期的函数不一样。
MSSQL获得当前日期：convert(varchar(10),Getdate(),120)
MYSQL获得当前日期：date(now())
Oracle获得当前日期：to_char(sysdate,'yyyy-mm-dd')
Access获得当前日期：date()
3、在各个数据库里获得当天的记录写法为(假设表名为：Table_1，日期列名为：date_col)：
MSSQL获得当天记录：
select * from table_1 where date_col>=convert(varchar(10),Getdate(),120) and date_col<convert(varchar(10),dateadd(d,1,Getdate()),120)
MYSQL获得当天记录：
select * from table_1 where date_col>=date(now()) and date_col<DATE_ADD(date(now()),INTERVAL 1 DAY)
Oracle获得当天记录：
select * from table_1 where date_col>=to_char(sysdate,'yyyy-mm-dd') and date_col<to_char(sysdate+1,'yyyy-mm-dd')
Access获得当天记录：
select * from table_1 where date_col>=date() and date_col<DateAdd("d",1,date())4、另外，在查询的时候，尽量不要对列进行运算，因为日期列上若有索引，就无法使用索引了。
数据库服务器时间是正确，getdate()取的是数据库服务器日期时间
你的语法没错啊，transday是datetime 类型吧
SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate
结果1
select * from [transaction] where transday=subStr(GETDATE(),0,10)
select * from [transaction] where subStr(GETDATE(),0,10) like transday||'%'
绛旓細-鏌ヨ鍓30澶╃殑鏁版嵁锛圫QLServer锛夛細SELECT*FROMAWHEREDATEDIFF(d,datetime,GETDATE())=30;-鏌ヨ涓婁竴鏈堢殑鏁版嵁锛圫QLServer锛夛細SELECT*FROMAWHEREDATEDIFF(m,shijian,GETDATE())=1;鍙︿竴绉鏌ヨ褰撳ぉ璁板綍鐨勬柟娉曟槸浣跨敤鏃堕棿鎴筹紝濡傚湪Access涓細SELECT*FROMj_GradeShopWHEREGAddTimeBETWEENCONVERT(datetime,LEFT(GETDA...
绛旓細1銆丼QL鍦ㄦ煡璇㈠綋澶╄褰曟椂瑕佹敞鎰忔槸浠庡綋澶╃殑0鐐0鍒0绉0姣寮濮嬶紝鍒版鏃0鐐0鍒0绉0姣鎴锛屼絾涓嶅寘鍚鏃ョ殑0鐐0鍒0绉0姣銆2銆佹敞鎰忥細鍦ㄤ笉鍚屾暟鎹簱浜у搧涓紝鑾峰緱褰撳ぉ鏃ユ湡鐨勫嚱鏁颁笉涓鏍枫侻SSQL鑾峰緱褰撳墠鏃ユ湡锛歝onvert(varchar(10),Getdate(),120)MYSQL鑾峰緱褰撳墠鏃ユ湡锛歞ate(now())Oracle鑾峰緱褰撳墠鏃ユ湡锛歵...
绛旓細SELECT * FROM Table WHERE time= DATE_FORMAT(NOW(),'%Y-%m-%d')浣跨敤date_format涓昏鏄负浜嗘牸寮忓寲鏃堕棿锛屼娇涓よ竟鐨勬椂闂存牸寮忎竴鏍凤紝鎴戝缓璁繕鏄牸寮忓寲涓涓嬫瘮杈冨ソ锛岄槻姝㈠嚭閿
绛旓細1銆佹煡璇㈠綋澶╃殑鎵鏈夋暟鎹 2銆佹煡璇㈡槰澶╃殑鎵鏈夋暟鎹 3銆佹煡璇㈡湭鏉ョn澶╃殑鎵鏈夋暟鎹 缁撴瀯鍖栨煡璇㈣瑷(Structured Query Language)绠绉癝QL锛屾槸涓绉嶇壒娈婄洰鐨勭殑缂栫▼璇█锛屾槸涓绉嶆暟鎹簱鏌ヨ鍜岀▼搴忚璁¤瑷锛岀敤浜庡瓨鍙栨暟鎹互鍙婃煡璇佹洿鏂板拰绠＄悊鍏崇郴鏁版嵁搴撶郴缁熴傜粨鏋勫寲鏌ヨ璇█鏄珮绾х殑闈炶繃绋嬪寲缂栫▼璇█锛屽厑璁哥敤鎴峰湪楂樺眰鏁版嵁缁...
绛旓細1銆侀鍏堝湪妗岄潰涓婏紝鐐瑰嚮鈥淢anagement Studio鈥濆浘鏍囥2銆佷箣鍚庡湪璇ョ晫闈腑锛岀偣鍑诲乏涓婅鈥滄柊寤鏌ヨ鈥濋夐」銆3銆佹帴鐫鍦ㄨ鐣岄潰涓紝杈撳叆鏄ㄥぉ锛屼粖澶╋紝鏄庡ぉ鍜屾湰鍛ㄧ殑璁板綍鐨sql璇彞鈥渟elect * from test2 where time = DATEADD( day,-1, convert(varchar(10),getdate(),120) ); select * from test2 where ...
绛旓細SQL鏈嶅姟鍣細Select锛奻romtablewhere锛2008锛7锛1锛囧拰锛2008锛12锛31锛囪闂細浠庤〃涓夋嫨鍙戠敓鏃ユ湡>#2008-7-1#鍜屽彂鐢熸棩鏈<#2008-12-31 灏辨槸杩欐牱锛氭敞鎰忥紝SQLserver涓殑鏃ユ湡鍜岃闂湁涓鐐逛笉鍚屻
绛旓細sql server锛歴elect * from 琛 where 鍙戠敓鏃ユ湡>'2008-7-1' and 鍙戠敓鏃ユ湡<'2008-12-31'access锛歴elect * from 琛 where 鍙戠敓鏃ユ湡>#2008-7-1# and 鍙戠敓鏃ユ湡<#2008-12-31 杩欐牱灏卞彲浠ヤ簡锛屾敞鎰弒ql server涓巃ccess涓殑鏃ユ湡鏈変竴鐐逛笉涓鏍枫
绛旓細insert into ...select ..., count(*) from 琛 where date >= trunc(sysdate )and date < trunc(sysdate) + 1;
绛旓細鏌ョ殑鏃跺欑敤鏃堕棿杞崲鍑芥暟鍚у瓧绗︿覆鏍煎紡鐨勬椂闂磋浆鎴恉ate绫诲瀷鐨勶紝灏卞ソ浜嗐傚啀鏈塲ava涓湁涓撻棬澶勭悊鏁版嵁搴揹ate绫诲瀷鏁版嵁鐨勭被銆俲ava.sql.Date銆傛垨鑰呬篃鍙互浣跨敤杩嘥imestamp銆
绛旓細select top(1)* from 琛ㄥ悕 order by 鏃ユ湡瀛楁 desc
