Converting Dates in SQL Server

 

SELECT 

   GETDATE() AS DefaultFormat,

   CONVERT(nvarchar(30), GETDATE(), 101) AS US,

   CONVERT(nvarchar(30), GETDATE(), 103) AS UK,

   CONVERT(nvarchar(30), GETDATE(), 111) AS Japan,

   CONVERT(nvarchar(30), GETDATE(), 104) AS German,

   CONVERT(nvarchar(30), GETDATE(), 112) AS ISO,

   CONVERT(nvarchar(30), GETDATE(), 109) AS Date_with_Milliseconds

Source

The Ten Most Asked SQL Server Questions And Their Answers

Link

SQL Server – inserting multiple rows with one statement

This is a nice trick:

INSERT INTO tblTable (rowName1, rowName2)
SELECT 'val1', 'val2'
UNION ALL
SELECT 'val3', 'val4'
UNION ALL
SELECT 'val5', 'val6'

This code will insert 3 rows into the table.

SQL Server 2005- OVER() partition

This will be useful for GridView pagination:

An aggregate functions can be added to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function.

This means that this code:

   1: select 

   2:     o.customerID, o.productID, o.orderDate, o.orderAmount, t.Total

   3: from 

   4:     Orders o

   5: inner join 

   6:    (

   7:     select customerID, sum(orderAmount) as Total from Orders group by customerID

   8:    ) 

   9:   t on t.customerID = o.customerID

can be replaced with:

   1: select customerID,  productID, orderDate, orderAmount, 

   2:       sum(orderAmount) OVER (Partition by CustomerID) as Total

   3: from Orders

Source

SQL - substring and charindex

 

   1: SELECT

   2:     substring(IdeaText,1,

   3:         charindex('//', IdeaText)

   4:     ) as part1

   5: FROM tblIdeas

 

substring

charindex

Save and Retrieve Images from the Database using ASP.NET

Source