常用SQL参考

##常用SQL

--日期
select 
YEAR(getdate()) yyyy, --int
MONTH(getdate()) MM, --int
DAY(getdate()) d, --int
LEFT(CAST( GETDATE() as date),7) m,
Convert(Varchar(6),GETDATE(),112) m2, 
CAST( GETDATE() as date)d1
,CONVERT(varchar(10),getdate(),120) d2,
Convert(Varchar(8),GETDATE(),112) d3,
CONVERT(varchar(13),getdate(),120) dh
/*
m              m2     d1         d2         d3       dh
-------------- ------ ---------- ---------- -------- -------------
2012-01        201201 2012-01-01 2012-01-01 20120101 2012-01-01 15
*/
--增加一天
select GETDATE()+1

--select DATEDIFF(ss,'2011-11-17','2011-11-18')
--select DATEDIFF(ss,'2011-11-17 23:59','2011-11-18')
--select DATEDIFF(ss,'2011-11-17 23:59:59','2011-11-18')
--select DATEDIFF(MS,'2011-11-17 23:59:59','2011-11-18')