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/

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());
		}
	}
}

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

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

MsSQL 雙層子查詢 當作主查詢的欄位條件值 產生錯誤

雙層子查詢單獨運作可以正常執行,但是將雙層子查詢的結果用來當作主查詢的欄位條件值時,就會出現錯誤。使用單一層子查詢來當作主查詢的條件值時又不會出錯。 最後只好修改雙層子查詢的語法,替換為兩個查詢條件做(and)的動作。
繼續閱讀