SQL 連線字串 參考頁

From: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49029

I have to develop an application such that i have to use the port number in the sql connection string in asp.net.How to specify the string plz help me out.

One way to do it is to connect via the IP address.

“Data Source=122.122.122.122,1433;Network Library=DBMSSOCN;Initial Catalog=Northwind;User ID=YourUser Password=YourPassGoesHere;"

(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))

Here’s a great site for more info: http://www.connectionstrings.com/

Mssql other instance db engine, how to set remotly connect.

MSSQL 非預設的資料庫實體 該如何使用資料庫管理工具遠端登入

如果MSSQL SERVER有超過兩個實體,預設的實體可以正常使用1433端口由遠端登入管理。

但是其它資料庫實體該如何遠端登入呢?

先了解另一個資料庫實體是否有啟用TCP通道進行連線,若有該通道是使用靜態連接埠或是系統自動給的動態連接埠

可以執行「資料庫組態管理工具」查詢得知。還有一點就是一般伺服器都會啟用防火牆規則。所以得知另一個資料庫實體的TCP通訊埠後,需要設定防火牆規則讓另一個資料庫實體的連接埠能對外開放連線

遠端如何連接呢?

伺服器欄位: 下面是資料庫實體名稱後面接的是「逗號」才輸入連接埠號。
192.168.128.219\HRM,50502
伺服器IP位址\實體名稱,連接埠號

填上有權限的帳號及密碼就能正常登入了。

重設 mysql table auto_increment 初值

出至:http://ariejan.net/2007/11/30/mysql-reset-the-auto-increment-value-of-a-table/

MySQL: (Re)set the auto-increment value of a table
Written by Ariejan de Vroom on 30 November 2007.

via Ad Packs
Sometimes it’s necessary to set the starting point of a MySQL auto-increment value.

Normally, MySQL starts auto-incrementing at 1. But let’s say you want to start at 10.000, because you want at least a five figure number. You can use the following query to set the MySQL auto-index:

ALTER TABLE some_table AUTO_INCREMENT=10000

If you want to delete all records from your table and restart auto-index at 1, you might be tempted to run a DELETE query, followed by the above example, setting the auto increment value to 1. There is a shortcut, however:

TRUNCATE TABLE some_table

This will basically reset the table, deleting all data and resetting the auto increment index. Do not that the truncate command is a hard-reset option. For instance, any triggers “ON DELETE” will not be fired when using truncate.

Easyflow ISO 直接開啟文件PDF發佈檔(自訂網頁程式)

在 Easyflow ISO 模組上,把開放給使用者能列印(直接開啟PDF)的表單,文件權限等級為標記「9」。

建立後端程式碼處理pdf,由資料庫讀取PDF字串並輸出到前端。

using System;
using System.Collections.Generic;
//using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class functions_readStream : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //Response.Write("功能尚未開放,請耐心等候。");
        //Response.End();
        try
        {
            string DocNO = Request.QueryString["DocNO"];
            string DocVer = Request.QueryString["DocVer"];

            using (SqlConnection myConnection = new SqlConnection("User ID=sa;Password = qwe19735;Data Source=192.168.128.219;DataBase=EFNETDB;"))
            {
                if (Session["IsFromLoginPage"] == null || Session["IsFromLoginPage"].ToString() != "Y")
                {
                    Response.Write("你尚未登入Easyflow或是Session已逾時。<br/>如有任何疑問請聯繫資訊課人員。");
                    Response.End();
                }

                const string SQL = @"select isoebb005,isoebb006,isoebb009,isoebb010,isoebf003,isoebf004
                from isoebb join isoebf on isoebb005=isoebf001 and isoebb006=isoebf002
                where isoebb005=@DocNO and isoebb006=@DocVer
                and (isoebf003='0' or (isoebf003='1' and isoebf004 in (select resan001 from resan where resan003=@UserId)))
                and isoebf005='9'
                and isoebb015='1'
                    ;";
                SqlCommand myCommand = new SqlCommand(SQL, myConnection);
                myCommand.Parameters.AddWithValue("@DocNO", DocNO);
                myCommand.Parameters.AddWithValue("@DocVer", DocVer);
                myCommand.Parameters.AddWithValue("@UserId", Session["strUserID"].ToString());

                myConnection.Open();
                SqlDataReader myReader = myCommand.ExecuteReader();

                if (myReader.Read())
                {
                    //Response.ContentType = myReader["MIME"].ToString();
                    Response.ContentType = "application/pdf";
                    Response.BinaryWrite((byte[])myReader["isoebb010"]);
                    Response.End();
                }
                else{
                    Response.Write("你沒有權限瀏覽與列印所選取的文件。<br/>如有任何疑問請聯繫資訊課人員。");
                }

                myReader.Close();
                myConnection.Close();
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
}

修改一般第三階表單判斷文件權限等級是否為 “9″ ,來產生列印的文件的連結。

using System;
using System.Collections.Generic;
//using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

public partial class MIS_ISO_Doc_Index : System.Web.UI.Page
{
    SqlConnection myConEFnet;
    SqlDataAdapter myISOAdapter;
    public string tmpstring, isoType, SessionValues;
    string sqlstring1 = @"
select
a.isoebe003,a.isoebe004,a.isoead003,a.isoeba003, a.isoebe001,a.isoeba002,
b.isoeba003 as isoeba003x,b.isoeba001 as isoeba001x,b.isoeba002 as isoeba002x ,b.isoebd003,isoebf005,in_groups
from
(select isoebe002,isoebe003, isoebe004,isoead003,isoebe001,isoeba002,isoeba003,isoeba012 from
(isoebe join isoead on isoebe003=isoead001 and isoebe004=isoead002 ) join isoeba on isoebe001=isoeba001
and isoebe002=isoeba002 where isoebe001 not like '%-%-%'  and isoeba099='2'
) as a left join
(select substring(isoeba001,0,LEN(isoeba001)-CHARINDEX('-',reverse(isoeba001))+1) as isoeba000,
isoeba001,isoeba002,isoeba003,isoebd003,isoebf005,
in_groups=(case when (isoebf003='0' or (isoebf003='1' and isoebf004 in (select resan001 from resan where resan003=@UserId))) then '1' else '0' end)
from isoeba left join isoebd on isoeba001=isoebd001 and isoeba002=isoebd002 left join isoebf on isoeba001=isoebf001 and isoeba002=isoebf002
where isoeba001 like '%-%-%' and isoeba099='2'
) as b on a.isoebe001=b.isoeba000
where a.isoebe003=@isoType
order by isoebe003,isoebe004,isoebe001,isoeba001x
";
    string sqlstring2 = @"
select
a.isoebe003,a.isoebe004,a.isoead003,a.isoeba003, a.isoebe001,a.isoeba002,
b.isoeba003 as isoeba003x,b.isoeba001 as isoeba001x,b.isoeba002 as isoeba002x ,b.isoebd003,isoebf005,in_groups
from
(select isoebe002,isoebe003, isoebe004,isoead003,isoebe001,isoeba002,isoeba003,isoeba012 from
(isoebe join isoead on isoebe003=isoead001 and isoebe004=isoead002 ) join isoeba on isoebe001=isoeba001
and isoebe002=isoeba002 where isoebe001 not like '%-%-%'  and isoeba099='2'
) as a left join
(select substring(isoeba001,0,LEN(isoeba001)-CHARINDEX('-',reverse(isoeba001))+1) as isoeba000,
isoeba001,isoeba002,isoeba003,isoebd003,isoebf005,
in_groups=(case when (isoebf003='0' or (isoebf003='1' and isoebf004 in (select resan001 from resan where resan003=@UserId))) then '1' else '0' end)
from isoeba left join isoebd on isoeba001=isoebd001 and isoeba002=isoebd002 left join isoebf on isoeba001=isoebf001 and isoeba002=isoebf002
where isoeba001 like '%-%-%' and isoeba099='2'
) as b on a.isoebe001=b.isoeba000
where a.isoebe003='ISO'
order by isoebe003,isoebe004,isoebe001,isoeba001x
";

    DataTable myDataTable1,myDataTable2;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["IsFromLoginPage"] == null || Session["IsFromLoginPage"].ToString() != "Y")
        {
            Response.Write("你尚未登入Easyflow或是Session已逾時。<br/>如有任何疑問請聯繫資訊課人員。");
            Response.End();
        }

        isoType="14001";
        if (Request.QueryString["isoType"] != null)
        {
            isoType = Request.QueryString["isoType"];
        }

        myConEFnet = new SqlConnection("User ID=sa;Password = password;Data Source=192.168.128.219;DataBase=EFNETDB;");
        SqlCommand myCommand1 = new SqlCommand(sqlstring1, myConEFnet);
        myCommand1.Parameters.AddWithValue("@isoType",isoType);
        myCommand1.Parameters.AddWithValue("@UserId", Session["strUserID"].ToString());
        SqlCommand myCommand2 = new SqlCommand(sqlstring2, myConEFnet);
        myCommand2.Parameters.AddWithValue("@UserId", Session["strUserID"].ToString());

        myConEFnet.Open();
        myISOAdapter = new SqlDataAdapter(myCommand1);
        myDataTable1 = new DataTable();
        myISOAdapter.Fill(myDataTable1);
        myISOAdapter = new SqlDataAdapter(myCommand2);
        myDataTable2 = new DataTable();
        myISOAdapter.Fill(myDataTable2);
        string[] strs;
        DataView tmpView1 = myDataTable1.DefaultView;
        DataTable tmpTable1 = tmpView1.ToTable("tmpTable1",true,strs= new string[]{"isoebe004","isoead003"});
        DataTable tmpTable2 = tmpView1.ToTable("tmpTable2", true, strs = new string[] { "isoebe004","isoeba003","isoebe001","isoeba002" });
        DataTable tmpTable3 = tmpView1.ToTable("tmpTable3", true, strs = new string[] { "isoebe001", "isoeba003x", "isoeba001x", "isoeba002x", "isoebd003", "isoebf005","in_groups" });
        DataView tmpView2 = myDataTable2.DefaultView;
        DataTable tmpTable4 = tmpView2.ToTable("tmpTable4",true,strs= new string[]{"isoebe004","isoead003"});
        DataTable tmpTable5 = tmpView2.ToTable("tmpTable5", true, strs = new string[] { "isoebe004","isoeba003","isoebe001","isoeba002" });
        DataTable tmpTable6 = tmpView2.ToTable("tmpTable6", true, strs = new string[] { "isoebe001", "isoeba003x", "isoeba001x", "isoeba002x", "isoebd003", "isoebf005","in_groups" });

        foreach (DataRow row in tmpTable1.Rows) {
            tmpstring += "<tr><td class='td1'><div>" + row["isoebe004"] + " " + row["isoead003"] + "</div></td><td><table>";
            foreach (DataRow row1 in tmpTable2.Select("isoebe004 = '"+ row["isoebe004"]+"'")) {
                tmpstring += "<tr><td class='td2'><div><a style='color:blue;' target='_blank' href='/efnet/src/ISO/ISOEM008/DocDetail.aspx?DocNO=" + row1["isoebe001"] + "&DocVer=" + row1["isoeba002"] + "'>" + row1["isoeba003"] + "</div></td><td class='td3'><div>" + row1["isoebe001"] + "</div></td><td  class='td4'><div>" + row1["isoeba002"] + "</div></td><td><table>";
                foreach(DataRow row2 in tmpTable3.Select("isoebe001 = '"+ row1["isoebe001"]+"'")){
                    if (row2["isoeba003x"].ToString() == "")
                    {
                        tmpstring += "<tr><td  class='td5'><div>&nbsp;</div></td><td  class='td6'><div>&nbsp;</div></td><td  class='td7'><div>&nbsp;</div></td></tr>";
                    }
                    else
                    {
                        tmpstring += "<tr><td  class='td5'><div><a style='color:blue;' target='_blank' href='/efnet/src/ISO/ISOEM008/DocDetail.aspx?DocNO=" + row2["isoeba001x"] + "&DocVer=" + row2["isoeba002x"] + "'>" + row2["isoeba003x"] + "</a>"+
						((row2["isoebd003"].ToString()!="")?"<a style='color:red;font-weight:bold;' target='_blank' href='../../frm/"+row2["isoebd003"].ToString()+"/"+row2["isoebd003"].ToString()+".aspx'>&nbsp;&nbsp;[申請]</a>":"")
                        + (row2["isoebf005"].ToString() == "9" && row2["in_groups"].ToString() == "1" ? "<a style='color:blue;' target='_blank' href='../functions/readStream.aspx?DocNO=" + row2["isoeba001x"] + "&DocVer=" + row2["isoeba002x"] + "''>&nbsp;&nbsp;[列印]</a>" : "")
                        +"</div></td><td  class='td6'><div>" + row2["isoeba001x"] + "</div></td><td  class='td7'><div>" + row2["isoeba002x"] + "</div></td></tr>";
                    }
                }
                tmpstring += "</table></td><td><table title='" + row1[1] + "'>";

				foreach (DataRow row3 in tmpTable5.Select("isoebe004 = '"+ row1[2].ToString().Replace("-","")+"'")) {
					tmpstring += "<tr><td class='td2'><div><a style='color:blue;' target='_blank' href='/efnet/src/ISO/ISOEM008/DocDetail.aspx?DocNO=" + row3["isoebe001"] + "&DocVer=" + row3["isoeba002"] + "'>" + row3["isoeba003"] + "</div></td><td class='td3'><div>" + row3["isoebe001"] + "</div></td><td  class='td4'><div>" + row3["isoeba002"] + "</div></td><td><table>";
					foreach(DataRow row4 in tmpTable6.Select("isoebe001 = '"+ row3["isoebe001"]+"'")){
						if (row4["isoeba003x"].ToString() == "")
						{
							tmpstring += "<tr><td  class='td5'><div>&nbsp;</div></td><td  class='td6'><div>&nbsp;</div></td><td  class='td7'><div>&nbsp;</div></td></tr>";
						}
						else
						{
							tmpstring += "<tr><td  class='td5'><div><a style='color:blue;' target='_blank' href='/efnet/src/ISO/ISOEM008/DocDetail.aspx?DocNO=" + row4["isoeba001x"] + "&DocVer=" + row4["isoeba002x"] + "'>" + row4["isoeba003x"] + "</a>"+
							((row4["isoebd003"].ToString()!="")?"<a style='color:red;font-weight:bold;' target='_blank' href='../../frm/"+row4["isoebd003"].ToString()+"/"+row4["isoebd003"].ToString()+".aspx'>&nbsp;&nbsp;[申請]</a>":"")
                            + (row4["isoebf005"].ToString() == "9" && row4["in_groups"].ToString() == "1" ? "<a style='color:blue;' target='_blank' href='../functions/readStream.aspx?DocNO=" + row4["isoeba001x"] + "&DocVer=" + row4["isoeba002x"] + "''>&nbsp;&nbsp;[列印]</a>" : "")
							+"</div></td><td  class='td6'><div>" + row4["isoeba001x"] + "</div></td><td  class='td7'><div>" + row4["isoeba002x"] + "</div></td></tr>";
						}
					}
                    tmpstring += "</table></td></tr>";
				}
				tmpstring += "</table></td></tr>";
            }
            tmpstring+="</table></td></tr>";
        }
        tmpstring="<table class='table1' style='display:none;'>"+tmpstring+"</table>";
        foreach (string SessionVar in Session.Keys)
        {
            SessionValues += "<br />" + SessionVar + ":" + Session[SessionVar].ToString();
        }
        SessionValues = "<div>" + SessionValues + "</div>";
    }
}

接著在機密等級代號維護作業中,新增一條文件權限等級「9」自訂公開、可列印。 新增一條機密等級代號9允許列印

指定允許被開啟列印的ISO文件,將文件的機密等級設定為「9」。指定允許列印的表單文件權限為「9 」

最後就完成了,ISO文件一覽表中加載了列印連結。ISO文件一覽表增加表單列印

c# read binary data from sqlserver and output data-stream

From: http://dbtutorials.com/advanced/retrieving-binary-sql-cs.aspx

c# read binary data from sqlserver

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class ShowImage : System.Web.UI.Page
{
	protected void Page_Load(object sender, EventArgs e)
	{
		try
		{
			int PictureID = Convert.ToInt32(Request.QueryString["ID"]);

			using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
			{
				const string SQL = "SELECT [MIME], [BinaryData] FROM [BinaryTable] WHERE [ID] = @ID";
				SqlCommand myCommand = new SqlCommand(SQL, myConnection);
				myCommand.Parameters.AddWithValue("@ID", PictureID);

				myConnection.Open();
				SqlDataReader myReader = myCommand.ExecuteReader();

				if (myReader.Read())
				{
					Response.ContentType = myReader["MIME"].ToString();
					Response.BinaryWrite((byte[]) myReader["BinaryData"]);
				}

				myReader.Close();
				myConnection.Close();
			}
		}
		catch (Exception ex)
		{
			Response.Write(ex.ToString());
		}
	}
}

linqtoexcel 讓讀取Excel資料像處理資料庫一樣簡單

Linq to Excel

Use LINQ to retrieve data from spreadsheets.

var excel = new ExcelQueryFactory("excelFileName");
var indianaCompanies = from c in excel.Worksheet<Company>()
                       where c.State == "IN"
                       select c;

Download the current version or install the NuGet package.

Go to the Read me page for information on implementing Linq to Excel in your project.

Need help? Report an issue or send questions to the discussion group.


Demo Video

SQL datetime convert to string format

SQL 日期時間轉換字串格式

YYYY-MM-DD (2008-06-26):CONVERT(CHAR(10), GETDATE(), 120)
HH:MM:SS:sss (17:24:30):CONVERT(CHAR(8), GETDATE(), 114)

MM DD YYYY (06 26 2008):CONVERT(CHAR(10), GETDATE(), 100)
MM/DD/YYYY (06/26/2008):CONVERT(CHAR(10), GETDATE(), 101)
MM.DD.YYYY (06.26.2008):CONVERT(CHAR(10), GETDATE(), 102)

DD/MM/YYYY (14/03/2011):CONVERT(CHAR(10), GETDATE(), 103)
DD.MM.YYYY (14.03.2011):CONVERT(CHAR(10), GETDATE(), 104)
DD-MM-YYYY (14-03-2011):CONVERT(CHAR(10), GETDATE(), 105)
DD MM YYYY (14 03 2011):CONVERT(CHAR(10), GETDATE(), 106)

YYYYMMDD (20110314):CONVERT(CHAR(8), GETDATE(), 112)
HH:MM:SS:sss (17:24:30:923):CONVERT(CHAR(12), GETDATE(), 114)

Sybase
YYYYMMDD (20080626):CONVERT(CHAR(10), GETDATE(), 112)
YYYY/MM/DD (2008/11/24):CONVERT(CHAR(10), GETDATE(), 111)
MM-DD-YYYY (11-24-2008):CONVERT(CHAR(10), GETDATE(), 110)

HH:mm:SS (15:49:34):CONVERT(CHAR(10), GETDATE(), 108)

來源:http://msdn.microsoft.com/zh-tw/library/a87d0850-c670-4720-9ad5-6f5a22343ea8

FreeBSD mysql 忘記密碼 mysqld_safe 安裝模式重設密碼。

如果忘記 MySQL root 密碼,可用下面方式重新設定

MySQL 服務停止,再以安全模式啟動

<span style="color: #3366ff;">killall mysqld</span>
<span style="color: #3366ff;">/usr/local/bin/mysqld_safe -u root --skip-grant-tables &amp; mysql</span>

重新 MySQL 設定 root 密碼

<span style="color: #3366ff;">&gt;use mysql</span>
<span style="color: #3366ff;"> &gt;UPDATE user SET password=password('new password') where user='root';</span>
<span style="color: #3366ff;"> &gt;FLUSH PRIVILEGES;</span>

請小心使用!!

MSSQL 使用 OPENROWSET 插入或更新檔案到資料型態是「varbinary(max)」的欄位

由於Easyflow的ISO文管人員在沒有注意文件內容是否正確的情況下就將新版文件發佈,隔天才發現發佈檔案有問題。但依照Easyflow ISO文管運作流程,當文件發佈後如果需要進行修正就要做一次文件版本更新。這樣的作法費時又費工,如果可以直接置換存放於資料庫中的檔案內容或許是一個比較簡的作法。

在MSSQL的資料庫中,可以利用內建函式「OPENROWSET」來將檔案塞入到欄位中。使用「OPENROWSET」可以當作是一個子查詢,而這個子查詢的來源資料表就是實體檔案的存放路徑(以SQLSERVER本機的路徑為基準、或是可以存取的網芳資料夾中的檔案路徑)。

注意!必須要給子查詢指定一個別名,否則在執行操作中會出現錯誤。

以下的範例是置換文件編號為「TQP-G001」發佈版本為「C」所對應到的發佈檔案,置換為網芳分享資料夾中的檔案。

update isoebb set isoebb010 =
(select * FROM OPENROWSET(bulk N'\\192.168.128.189\temp\廢棄物管理程序.pdf', SINGLE_BLOB ) as binary_file )
where isoebb005='TQP-G001' and isoebb006='C'

SQL 字串樣式轉換為日期格式 CAST 和 CONVERT

字串格式轉換為日期格式範例:

-- SQL Server string to date / datetime conversion - datetime string format sql server
-- MSSQL string to datetime conversion - convert char to date - convert varchar to date
-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)
SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000
 
-- Without century (yy) string date conversion - convert string to datetime function
SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000
 
-- Convert string to datetime sql - convert string to date sql - sql dates format
-- T-SQL convert string to datetime - SQL Server convert string to date
SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy
-- mon types are nondeterministic conversions, dependent on language setting
SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy
SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss
-- 1900-01-01 20:10:44.000
 
-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format
SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013
SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy
SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd
-- YYYYMMDD ISO date format works at any language setting - international standard
SELECT convert(datetime, '20161023')
SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm
-- 2016-10-23 11:02:07.577
SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)
-- 1900-01-01 20:10:25.300
SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
-- 2016-10-23 20:44:11.000
SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
-- 2016-10-23 20:44:11.500
 
-- Style 126 is ISO 8601 format: international standard - works with any language setting
SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)
-- 2008-10-23 18:52:47.513
SELECT convert(datetime, N'23 شوال 1429  6:52:47:513PM', 130) -- Islamic/Hijri date
SELECT convert(datetime, '23/10/1429  6:52:47:513PM',    131) -- Islamic/Hijri date
 
-- Convert DDMMYYYY format to datetime - sql server to date / datetime
SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)
-- 2016-01-31 00:00:00.000
-- SQL Server T-SQL string to datetime conversion without century - some exceptions
-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc
SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default
SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.
SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI
SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR
SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German
SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian
SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.
SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.
SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss
SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec
SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.
SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan
SELECT convert(datetime, '161023', 12) -- yymmdd ISO
SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt
SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)
SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.
SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC
------------

-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss
-- String to datetime - mssql datetime - sql convert date - sql concatenate string
DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)
 
SELECT @DateValue = '20120718',
       @TimeValue = '211920'
SELECT @DateTimeValue =
convert(varchar, convert(datetime, @DateValue), 111)
+ ' ' + substring(@TimeValue, 1, 2)
+ ':' + substring(@TimeValue, 3, 2)
+ ':' + substring(@TimeValue, 5, 2)
SELECT
DateInput = @DateValue, TimeInput = @TimeValue,
DateTimeOutput = @DateTimeValue;
/*
DateInput   TimeInput   DateTimeOutput
20120718    211920      2012/07/18 21:19:20 */

資料來源:http://www.sqlusa.com/bestpractices/datetimeconversion/
http://technet.microsoft.com/zh-tw/library/ms187928.aspx