如何可以在以下的SQL多加一欄顯示學生某天如:05/01/2022當天的年齡? select a.CHNAME '中文姓名', a.ENNAME '英文姓名', a.SEX '性別', a.CLASSCODE '班別', a.CLASSNO '班號', dateformat(a.DOB, 'DD/MM/YYYY') '出生日期', a.HKID, dateformat(d.FIRSTATTDATE, 'DD/MM/YYYY') '入學日期' from VW_STU_LATESTSTUDENT a left outer join (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from TB_STU_STUSCHREC group by STUID) d on d.STUID = a.STUID where a.SCHYEAR=? order by a.CLASSLVL, a.CLASSCODE, a.CLASSNO
你好, 可以參考一下以下的SQL 這條SQL用了NOW()的FUNCTION獲取執行SQL當日的日期,再與出生日期計算出 "由出生日直到今天的日數",再除365.25 轉化為年份,就得出了學生的歲數。 select a.CHNAME '中文姓名', a.ENNAME '英文姓名', a.SEX '性別', a.CLASSCODE '班別', a.CLASSNO '班號', dateformat(a.DOB, 'DD/MM/YYYY') '出生日期', a.HKID, dateformat(d.FIRSTATTDATE, 'DD/MM/YYYY') '入學日期', (DATEDIFF(dd, dateformat(a.DOB, 'YYYY/MM/DD'), CONVERT(CHAR(12), NOW(), 111)))/365.25 as Student_Age from wsadmin.VW_STU_LATESTSTUDENT a left outer join (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from wsadmin.TB_STU_STUSCHREC group by STUID) d on d.STUID = a.STUID where a.SCHYEAR=? order by a.CLASSLVL, a.CLASSCODE, a.CLASSNO