SQL Date and Time Functions

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)

-- Oct 2 2010 11:01AM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

-- Oct 2 2010 11:02:44:013AM

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

-- yyyymmdd - ISO date format - international standard - works with any language setting

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

-- 02 Oct 2010 11:02:07:577

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

-- 2010-10-02T10:52:47.513

-- Without century (YY) date / datetime conversion - there are exceptions!

SELECT convert(varchar, getdate(), 0) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(varchar, getdate(), 1) -- mm/dd/yy

SELECT convert(varchar, getdate(), 2) -- yy.mm.dd

SELECT convert(varchar, getdate(), 3) -- dd/mm/yy

SELECT convert(varchar, getdate(), 4) -- dd.mm.yy

SELECT convert(varchar, getdate(), 5) -- dd-mm-yy

SELECT convert(varchar, getdate(), 6) -- dd mon yy

SELECT convert(varchar, getdate(), 7) -- mon dd, yy

SELECT convert(varchar, getdate(), 8) -- hh:mm:ss

SELECT convert(varchar, getdate(), 9) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 10) -- mm-dd-yy

SELECT convert(varchar, getdate(), 11) -- yy/mm/dd

SELECT convert(varchar, getdate(), 12) -- yymmdd

SELECT convert(varchar, getdate(), 13) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 14) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 20) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 21) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 22) -- mm/dd/yy hh:mm:ss AM (or PM)

SELECT convert(varchar, getdate(), 23) -- yyyy-mm-dd

SELECT convert(varchar, getdate(), 24) -- hh:mm:ss

SELECT convert(varchar, getdate(), 25) -- yyyy-mm-dd hh:mm:ss.mmm


-- SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd

SELECT convert(varchar(7), getdate(), 126) -- yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8) -- mon yyyy
SELECT substring(convert(varchar, getdate(), 120),6, 11) -- mm-dd hh:mm

------------

-- SQL Server date formatting function - convert datetime to string


CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

DECLARE @StringDate VARCHAR(32)

SET @StringDate = @FormatMask

IF (CHARINDEX ('YYYY',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'YYYY', DATENAME(YY, @Datetime))

IF (CHARINDEX ('YY',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'YY', RIGHT(DATENAME(YY, @Datetime),2))

IF (CHARINDEX ('Month',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'Month', DATENAME(MM, @Datetime))

IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

SET @StringDate = REPLACE(@StringDate, 'MON',

LEFT(UPPER(DATENAME(MM, @Datetime)),3))

IF (CHARINDEX ('Mon',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'Mon', LEFT(DATENAME(MM, @Datetime),3))

IF (CHARINDEX ('MM',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'MM',

RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

IF (CHARINDEX ('M',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'M',

CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

IF (CHARINDEX ('DD',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'DD',

RIGHT('0'+DATENAME(DD, @Datetime),2))

IF (CHARINDEX ('D',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'D', DATENAME(DD, @Datetime))

RETURN @StringDate

END

GO

What is the relation between tcp/ip and http ?

TCP is one of the core protocols of the Internet Protocol Suite. - TCP is one of the two original components of the suite (the other being Internet Protocol, or IP), so the entire suite is commonly referred to as TCP/IP. Whereas IP handles lower-level transmissions from computer to computer as a message makes its way across the Internet, TCP operates at a higher level, concerned only with the two end systems, for example a Web browser and a Web server. In particular, TCP provides reliable, ordered delivery of a stream of bytes from a program on one computer to another program on another computer. Besides the Web, other common applications of TCP include e-mail and file transfer. Among other management tasks, TCP controls segment size, flow control, and data exchange rate.

HTTP is an Application Layer protocol for distributed, collaborative, hypermedia information systems.
HTTP is a request-response standard typical of client-server computing. In HTTP, web browsers or spiders typically act as clients, while an application running on the computer hosting the web site acts as a server. The client, which submits HTTP requests, is also referred to as the user agent. The responding server, which stores or creates resources such as HTML files and images, may be called the origin server. In between the user agent and origin server may be several intermediaries, such as proxies, gateways, and tunnels.

HTTP is not constrained in principle to using TCP/IP, although this is its most popular implementation platform. Indeed HTTP can be "implemented on top of any other protocol on the Internet, or on other networks." HTTP only presumes a reliable transport; any protocol that provides such guarantees can be used.
Resources to be accessed by HTTP are identified using Uniform Resource Identifiers (URIs)—or, more specifically, Uniform Resource Locators (URLs)—using the http or https URI schemes.

The above is correct. but the way I see it is that the web pages both on the server end and your own end are written in hypertext markup language (HTML). When your computer asks a remote computer to send you a COPY of its webpage, you establish a connection with that remote computer using an agreed protocol called TCP/IP. As I understand it, the IP part connects the IP addresses together and then the TCP part establishes a sort of virtual connection between the two. Hypertext TRANSFER protocol (HTTP) is the agreed way (protocol) in which the remote computer copies its own data, packages it up and sends it down the virtual tube created by the TCP connection, and onwards to your own computer, at which time the (now) downloaded webpage is reassembled in your web browser from the data packets ( A packet is a sequence of bytes and consists of a header followed by a body. The header describes the packet's destination and, optionally, the routers to use for forwarding until it arrives at its final destination. The body contains the data IP is transmitting. ).
 
I'm not sure whether to describe the TCP as a hose-pipe and the HTTP as the water flowing in it but for newbies , this explanation might help get closer to the right concept.

Get Montha Name from a Number in SQL

DECLARE  @monthInt  int
SET @monthInt = 5
select datename(month,dateadd(month, @monthInt - 1, 0)) as MonthName

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Download Sample Apps

for dowload sample projects click on below link

http://cid-b86cc93bd207b055.skydrive.live.com/browse.aspx/.Public/VS%20Projects