sql ֪ʶ
select datediff(month,'2009-11-02','2009-12-01')
²»ÖªµÀÄãÒªÔõô¸öÔ²îÒì
ÉÏÃæµÄÃüÁî½á¹ûÒ²ÊÇ1
µ«ÊÇÆäʵ°´ÕÕ³£Ê¶²îÒì²»ÊÇÒ»¸öÔ£¬ÊDzîÒ»ÌìÒ»¸öÔÂ
ÕâÓжÎÏֳɵĴúÂë¿ÉÒÔ°ïÖúÄãËã³ö³£Ê¶ÉϵIJîÒì
SQL code
declare @t table(a datetime,b datetime);
insert @t
select '2009-11-02','2009-12-01' UNION ALL
select '2009-11-01','2009-12-01'
-->??
select a,b,
ltrim(case
when (month(b)=month(a) and day(b)>=day(a)) or month(b)>month(a)
then datediff(year,a,b)
else
datediff(year,a,b)-1
end)+'Äê'
+ltrim(case
when day(b)>=day(a) --and month(b)>=month(a)
then datediff(month,a,b)%12
else
(datediff(month,a,b)-1)%12
end)+'ÔÂ'
+ltrim(case
when day(b)>=day(a)
then day(b)-day(a)
else
datediff(dd,convert(varchar(8),dateadd(mm,-1,b),120)+'01',b)+1-day(a)
end)+'ÈÕ' as 'ÈÕÆÚ²î'
from
(select (case when a>b then b else a end) as a,
(case when a>b then a else b end) as b from @t ) t;
a b ÈÕÆÚ²î
2009-11-02 00:00:00.000 2009-12-01 00:00:00.000 0Äê0ÔÂ29ÈÕ
2009-11-01 00:00:00.000 2009-12-01 00:00:00.000 0Äê1ÔÂ0ÈÕ
Ïà¹ØÎĵµ£º
SQLÖеÄʱ¼äº¯Êý·Ç³£ÓÐÓã¬ÌرðÊÇÔÚÎÒÃǽøÐгõʼ¸³Öµ¡¢¸´ÔÓ²éѯµÄʱºò£¬¾ÍÏÔµÃÌر𷽱㡣
1¡¢»ñµÃϵͳµ±Ç°Ê±¼ä
select getdate()
2¡¢DateName ( datepart , date )·µ»Ø±íʾָ¶¨ÈÕÆÚµÄÖ¸¶¨ÈÕÆÚ²¿·ÖµÄ×Ö·û´®¡£
--½ñÌìÊÇ2009-2-24--ÐÇÆÚ¶þ
SELECT DATENAME(year, getdate()) AS 'Year&nbs ......
select case when c.colid=1 then object_name(c.id) else '' end as ±íÃû
,c.name as ×Ö¶ÎÃû
,t.name Êý¾ÝÀàÐÍ
,c.prec as ³¤¶È ......
µÚÒ»²¿·Ö
µ¥±í²éѯ
ÀýÒ»£º²éѯȫÌåѧÉúµÄѧºÅÓëÐÕÃû
SELECT Sno,Sname
from Student;
Àý¶þ£º²éѯȫÌåѧÉúµÄÐÕÃû¡¢Ñ§ºÅ¡¢ËùÔÚϵ
SELECT Sname,Sno,Sdept
from Student;
ÀýÈý£º²éѯȫÌåѧÉúµÄÏêϸ¼Ç¼
SELECT *
from Student;
µÈ¼ÛÓÚ£º
SELECT *
from Student;
ÀýËÄ£º²éѯȫÌåѧÉúµÄÐÕÃû¼°Æä³öÉúÄê·Ý
......
µÚ¶þ²¿·Ö
1.ËùÓÐÄÐÉúµÄÐÕÃû¡¢ÄêÁä:
Select Sname£¬Sage
from student;
2.ËùÓÐÄêÁä´óÓÚ20£¬¼ÆËã»ú¿ÆѧϵѧÉúÃûµ¥:
SELECT Sname
from Student
WHERE Sage>20 AND Sdept='CS';
3.³É¼¨´óÓÚ60µÄѧÉúѧºÅ:
SELECT Sno
from SC
WHERE Grade>60;
4.³É¼¨ÔÚ70µ½80Ö®¼äµÄѧÉúѧºÅ:
SELECT Sno
from SC
......