XiaoLian

//計算兩各日期的差異 DATEDIFF ( datepart , startdate , enddate ) Ex: select a.create_date, a.read_date, b.sub_id, b.p2_id, b.p2_name,b.is_close, b.from_date, b.to_date from jobs.dbo.jobs2_auto_match_log as a, jobs.dbo.jobs2 b  where a.sub_id=b.sub_id and (read_date is not null or read_date<>'') and (datediff(d,a.read_date,GETDATE()))<= 30 order by a.read_date desc Getdate() 是今天日期  為日期型態 --YYYY/MM/DD Select Convert ( varchar (10),Getdate(),111) from TableName --YYYYMMDD Select Convert ( varchar (10),Getdate(),112) from TableName --HH:MM:SS Select Convert ( varchar (8),Getdate(),108) from TableName --HH:MM:SS:mmm Select Convert ( varchar (12),Getdate(),114) from TableName Select   Convert ( varchar , convert ( money ,balance),1) from TableName 得到會是 轉成金額型式 ex: 1,029,527.00 ex: balance  為數字型態 參考資料: http://technet.microsoft.com/zh-tw/library/ms187928.aspx 貨幣格式 捨棄小數位 不含有錢字符號 SELECT PARSENAME ( Convert ( varchar , Convert ( money ,5550000.65),1),2) ==>5,...

欄位的值是NULL, 將會被轉成0 ISNULL(欄位名,0) EX: SELECT ProductName,UnitPrice*(UnitsInStock+ ISNULL (UnitsOnOrder,0)) FROM Products

IN, NOT IN : 後面接的子查詢只能接單一欄位的值   ex: SELECT pub_id FROM titles EXISTS, NOT EXISTS : 後面接的子查詢可以單一欄位的值,也可以完整的語句  ex: SELECT * FROM titles IN ,EXISTS : 存在於 ;  NOT IN , NOT EXISTS : 不存在於 Ex: SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5 SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID IN (1, 4, 5) 以上兩個是相同結果 SELECT DISTINCT pub_name FROM publishers WHERE EXISTS ( SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type ='business') SELECT distinct pub_name FROM publishers WHERE pub_id IN ( SELECT pub_id FROM titles WHERE type = 'business') 以上兩個是相同結果

Ex: 存在於不同 簡繁體DB     Chinese_PRC_CI_AS (簡體)     Chinese_Taiwan_Stroke_CI_AS (繁體)    SELECT Table1.*,Table2.* 　 FROM Table1 　 INNER JOIN Table2 ON Table2.FK = Table1.PK COLLATE Chinese_PRC_CI_AS     SELECT Table1.*,Table2.* 　　 FROM Table1 　　INNER JOIN Table2 ON Table2.FK = Table1.PK COLLATE Chinese_Taiwan_Stroke_CI_AS  not in 用法 : select T0.ItemCode from RDC090123.dbo.OITM T0 where T0.frozenFor='Y' and T0.ItemCode not in ( select distinct X.CN_CODE COLLATE Chinese_Taiwan_Stroke_CI_AS  from EMasterPDM.dbo.TN_NODE X where T0.ItemCode=X.CN_CODE COLLATE Chinese_Taiwan_Stroke_CI_AS ) not EXISTS 用法 : select T0.ItemCode from RDC090123.dbo.OITM T0 where T0.frozenFor='Y' and not EXISTS   ( select distinct X.CN_CODE COLLATE Chinese_Taiwan_Stroke_CI_AS  from EMasterPDM.dbo.TN_NODE X  where T0.ItemCode=X.CN_CODE COLLATE Chinese_Taiwan_Stroke_CI_AS)   以上兩個結果都一樣

function firstlettererr(id){    var fl=id.value.substr(0,1);    var T="ABCDEFGHJKLMNPQRSTUVWXYZ"; //*24個*//     var smp=id.value.substr(0,1);    if (T.indexOf(smp) == -1) { return false; } else { return true; } } function checkid(id) {    if(id.value.length==10)    { return true; } else { return false; } } function chfastid(id) {    var c = id.value.charAt(0);    if(c<"A" || c> "Z")    { return false; } else { return true; } } function ch12fastid(id) {    var c = id.value.charAt(1);     if(c!="1" && c!="2")    { return false; } else { return true; } } function idmanber(id) { //=====後九碼為數字//    var bmp;    var d="0123456789";    var bab=id.value.length-1;    for (var i=1;i<=bab;i++){      bmp=id.value.substr(i,1)      if (d.indexOf(bmp) == -1) { return ...

function checkDate (arg_intYear, arg_intMonth, arg_intDay) {      //月數從0開始，所以要將參數減一     var objDate = new Date(arg_intYear, arg_intMonth-1, arg_intDay);     //檢查月份是否小於12大於1     if((parseInt(arg_intMonth,10) > 12) || (parseInt(arg_intMonth,10) < 1))     {         return false;     }     else   {         //如果objDate日數進位不等於傳入的arg_intDay，代表天數格式錯誤，另外月份進位也代表日期格式錯誤         if((parseInt(arg_intDay,10) != parseInt(objDate.getDate(),10))||(parseInt(arg_intMonth,10)!= parseInt((objDate.getMonth()+1),10)))         {             return false;         }         else         {   ...