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         {   ...

Save water. Shower with your girlfriend. 要節約用水，和女友一起洗澡。 Love the neighbor. But don't get caught. 愛你的鄰居，不過別讓她老公抓到。 One should love animals. They are so tasty. 每個人都應該熱愛動物，牠們是如此美味。 「Your future depends on your dreams.」 So go tosleep. 「現在的夢想決定著你的將來」所以還是睡覺吧。 Behind every successful man, there is a woman.And behind every unsuccessful man, there are two. 每個成功男人的背後，都有一個女人。每個不成功男人的背後，都有兩個。 Love is photogenic. It needs darkness to develop. 愛情就像照片，需要黑暗來培養 。 Children in backseats cause accidents. Accidents in backseats cause children. 後排座位上的小孩會生出意外，後排座位上的意外會生出小孩。 Every man should marry. After all, happiness is not the only thing in life. 每個男人都應該結婚，畢竟快樂不是生活中唯一的追求。 The wise never marry， And when they marry they become otherwise. 聰明人不結婚，一旦結婚就不聰明了。 「Work fascinates me.」 I can look at it for hours！ 「工作好有意思耶！」我可以盯著看上好幾個小時(發呆)。 「Hard work never killed anybody.」 But why take the risk？ 「努力工作不會死的！」不過何必冒那個險呢？ Success is a relative term. It brings so many relatives. 成功是相對的，他帶來很多親戚。 Never put off t...