SQL Server 2008 New DATETIME DataTypes

The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are

• DATE
• TIME
• DATETIME2
• DATETIMEOFFSE

DATE Data Type

Property	Value
Syntax	date
Usage	DECLARE @MyDate date CREATE TABLE Table1 ( Column1 date )
Default string literal format	YYYY-MM-DD
Range	0001-01-01 through 9999-12-31 January 1, 1 A.D. through December 31, 9999 A.D.
Element ranges	YYYY is four digits from 0001 to 9999 that represent a year. MM is two digits from 01 to 12 that represent a month in the specified year. DD is two digits from 01 to 31, depending on the month, that represent a day of the specified month.
Character length	10 positions
Precision, scale	10, 0
Storage size	3 bytes, fixed
Storage structure	1, 3-byte integer stores date.
Accuracy	One day
Default value	1900-01-01 This value is used for the appended date part for implicit conversion from time to datetime2 or datetimeoffset.
Calendar	Gregorian
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

TIME Datatype

Property	Value
Syntax	time [ (fractional second precision) ]
Usage	DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) )
fractional seconds precision	Specifies the number of digits for the fractional part of the seconds. This can be an integer from 0 to 7. The default fractional precision is 7 (100ns).
Usage	DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) )
Default string literal format	hh:mm:ss[.nnnnnnn]
Range	00:00:00.0000000 through 23:59:59.9999999
Element ranges	hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
Character length	8 positions minimum (hh:mm:ss) to 16 maximum (hh:mm:ss.nnnnnnn)
Precision, scale (user specifies scale only)	Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision time(16,7)57 time(0)(8,0)30-2 time(1)(10,1)30-2 time(2)(11,2)30-2 time(3)(12,3)43-4 time(4)(13,4)43-4 time(5)(14,5)55-7 time(6)(15,6)55-7 time(7)(16,7)55-7
Storage size	5 bytes, fixed, is the default with the default of 100ns fractional second precision.
Accuracy	100 nanoseconds
Default value	00:00:00 This value is used for the appended time part for implicit conversion from date to datetime2 or datetimeoffset.
User-defined fractional second precision	Yes
Time zone offset aware and preservation	No
Daylight saving aware	No

DATETIME2 Data Type

Property	Value
Syntax	datetimeoffset [ (fractional seconds precision) ]
Usage	DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
Default string literal formats (used for down-level client)	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] For more information, see the "Backward Compatibility for Down-level Clients" section of Using Date and Time Data.
Date range	0001-01-01 through 9999-12-31 January 1,1 A.D. through December 31, 9999 A.D.
Time range	00:00:00 through 23:59:59.9999999
Time zone offset range	-14:00 through +14:00
Element ranges	YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59.
Character length	26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
Precision, scale	Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision datetimeoffset(34,7)107 datetimeoffset(0)(26,0)80-2 datetimeoffset(1)(28,1)80-2 datetimeoffset(2)(29,2)80-2 datetimeoffset(3)(30,3)93-4 datetimeoffset(4)(31,4)93-4 datetimeoffset(5)(32,5)105-7 datetimeoffset(6)(33,6)105-7 datetimeoffset(7)(34,7)105-7
Storage size	10 bytes, fixed is the default with the default of 100ns fractional second precision.
Accuracy	100 nanoseconds
Default value	1900-01-01 00:00:00 00:00
Calendar	Gregorian
User-defined fractional second precision	Yes
Time zone offset aware and preservation	Yes
Daylight saving aware	No

DATETIMEOFFSET Datatype

Property	Value
Syntax	datetimeoffset [ (fractional seconds precision) ]
Usage	DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
Default string literal formats	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
Date range	0001-01-01 through 9999-12-31 January 1,1 A.D. through December 31, 9999 A.D.
Time range	00:00:00 through 23:59:59.9999999
Time zone offset range	-14:00 through +14:00
Element ranges	YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59.
Character length	26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
Precision, scale	Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision datetimeoffset(34,7)107 datetimeoffset(0)(26,0)80-2 datetimeoffset(1)(28,1)80-2 datetimeoffset(2)(29,2)80-2 datetimeoffset(3)(30,3)93-4 datetimeoffset(4)(31,4)93-4 datetimeoffset(5)(32,5)105-7 datetimeoffset(6)(33,6)105-7 datetimeoffset(7)(34,7)105-7
Storage size	10 bytes, fixed is the default with the default of 100ns fractional second precision.
Accuracy	100 nanoseconds
Default value	1900-01-01 00:00:00 00:00
Calendar	Gregorian
User-defined fractional second precision	Yes
Time zone offset aware and preservation	Yes
Daylight saving aware	No

Difference between SMALLDATETIME and DATETIME

The differences are catagorize in three things.

1. Range of Dates

A DateTime can range from January 1, 1753 to December 31, 9999.
A SmallDateTime can range from January 1, 1900 to June 6, 2079.

2. Accuracy

DateTime is accurate to three-hundredths of a second.
SmallDateTime is accurate to one minute.

3. Size

DateTime takes up 8 bytes of storage space.
SmallDateTime takes up 4 bytes of storage space.

List of number of Triggers in a database

SELECT S2.[name] TableName, S1.[name] TriggerName,
CASE
WHEN S1.deltrig > 0 THEN 'Delete'
WHEN S1.instrig > 0 THEN 'Insert'
WHEN S1.updtrig > 0 THEN 'Update'
END 'TriggerType'
FROM sysobjects S1 JOIN sysobjects S2
ON S1.parent_obj = S2.[id]
WHERE S1.xtype='TR'

Function to return all dates consisting of n(th) week of the specific day

-- =============================================
-- Description: function to return date
-- Select * from dbo.GetAllDate(2009,5,5)
-- =============================================
CREATE FUNCTION [dbo].[GetAllDate]
(
@year INT, -- Year of Date
@number SMALLINT, -- (n)th Week of Date
@day SMALLINT -- Day of Date
)
RETURNS @ReturnTbl TABLE
(
Dates DATETIME
)
AS
BEGIN
/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/

IF @number > 5
BEGIN
SET @number = 5
END

DECLARE @start_date DATETIME, @retrunDate DATETIME, @month_counter TINYINT

SET @month_counter =1

WHILE (@month_counter < = 12)
BEGIN
SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month_counter AS VARCHAR(4)) + '-01' As SmallDateTime)

IF(MONTH(DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))=@month_counter)
BEGIN
IF(MONTH(DATEADD(WEEK,@number-1,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date)))=@month_counter)
SELECT @retrunDate = DATEADD(WEEK,@number-1,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
ELSE
SELECT @retrunDate = DATEADD(WEEK,@number-2,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
END
ELSE
BEGIN
IF(MONTH(DATEADD(WEEK,@number,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date)))=@month_counter)
SELECT @retrunDate = DATEADD(WEEK,@number,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
ELSE
SELECT @retrunDate = DATEADD(WEEK,@number-1,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
END

INSERT INTO @ReturnTbl(Dates) SELECT (@retrunDate)

SET @month_counter = @month_counter + 1

END

RETURN;

END

Function to return all specific dates in a specific year

-- =============================================
-- Description: Function to return date for all month
-- Select * from dbo.GetDateOfAllMonth(1997,29)
-- =============================================
CREATE FUNCTION [dbo].[GetDateOfAllMonth]
(
@year INT, -- Year of Date
@number SMALLINT -- (n)th Date
)
RETURNS @ReturnTbl TABLE
(
Dates DATETIME
)
AS
BEGIN

DECLARE @start_date DATETIME, @end_date DATETIME, @retrunDate DATETIME, @total_days TINYINT,@counter TINYINT, @month_counter TINYINT

SET @month_counter =1

WHILE (@month_counter < = 12)
BEGIN
SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month_counter AS VARCHAR(4)) + '-01' As SmallDateTime)

SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))

SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month_counter AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)

SET @counter =1

WHILE @start_date <= @end_date
BEGIN

IF(DATEPART(dd , @start_date )= @number) AND @counter <= @number
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dd , @start_date )
INSERT INTO @ReturnTbl(Dates) values (@start_date)
SET @counter = ISNULL(@counter,0)+1
END

SET @start_date = DATEADD(DAY,1,@start_date)
END

SET @month_counter = @month_counter + 1

END
--select * from @ReturnTbl
RETURN;

END

Function to create a date from month, year, day, n(th) week of the date.

-- =============================================
-- Description: function to return date
-- Select dbo.findDate(2,2009,1,4)
-- Select dbo.findDate(2,2012,3,7)
-- =============================================
CREATE FUNCTION [dbo].[findDate]
(
@month SMALLINT, -- Month of Date
@year INT, -- Year of Date
@day SMALLINT, -- Day of Date
@weekday SMALLINT -- (n)th Week of Date
)
RETURNS SmallDateTime
AS
BEGIN

/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/

DECLARE @start_date SmallDateTime, @end_date SmallDateTime, @retrunDate SmallDateTime, @total_days TINYINT,@counter TINYINT

SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-01' As SmallDateTime)

SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))

SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)

SET @counter =1

WHILE @start_date <= @end_date
BEGIN

IF(DATEPART(dw , @start_date )= @day) AND @counter <= @weekday
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dw , @start_date )

SET @retrunDate = @start_date
SET @counter = ISNULL(@counter,0)+1
END
-- SELECT @counter, @retDate
SET @start_date = DATEADD(DAY,1,@start_date)

END

--SELECT @retrunDate

RETURN @retrunDate

END

Give permission to create database diagram

EXEC sp_dbcmptlevel [DatabaseName], 90;
go
ALTER AUTHORIZATION ON DATABASE:: [DatabaseName] TO sa
go
use [DatabaseName]
go
EXECUTE AS USER = N'dbo' REVERT

Single update query for swaping one column of two different rows of a single table.

CREATE TABLE c_user (id INT, first_name VARCHAR(50))
INSERT INTO c_user VALUES (1,'Neeraj')
INSERT INTO c_user VALUES (2,'Vishal')

SELECT * FROM c_user WHERE id in (1,2)

UPDATE c_user
SET first_name = CASE
WHEN id = 1 THEN fn.f2
WHEN id = 2 THEN fn.f1
END
FROM c_user
OUTER APPLY
(
SELECT cu1.id cu1id, cu1.first_name f1, cu2.id cu2id, cu2.first_name f2
FROM c_user cu1,c_user cu2
WHERE cu1.id = 1 AND cu2.id = 2
) fn
WHERE c_user.id = fn.cu1id OR c_user.id = fn.cu2id

Single update query for swaping one column of two different rows,

New error handling method in SQLServer procedures from version 2005. This is the better way of handling errors in SQLServer.

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO




BEGIN TRY

BEGIN TRANSACTION -- Start the transaction

--insert
INSERT INTO....

--update
UPDATE INTO....



-- Generate divide-by-zero error.
SELECT 1/0;

-- If we reach here, success!
IF @@TRANCOUNT > 0
COMMIT TRANSACTION


END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;

END CATCH;

New way to write transaction in stored procedure:

SET XACT_ABORT ON

BEGIN TRAN
……………………………………
……………………………………
……………………………………

COMMIT TRAN

SET XACT_ABORT OFF

In this approach, you don’t need to check @@error and rollback inside transaction, it will automatically rollback the action when error happens.

Difference between RowNumber, Rank and Dense Rank

CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)

INSERT T VALUES (0, 1, 6)
INSERT T VALUES (0, 1, 4)
INSERT T VALUES (0, 3, 2)
INSERT T VALUES (0, 3, 0)
INSERT T VALUES (1, 0, 7)
INSERT T VALUES (1, 0, 5)
INSERT T VALUES (0, 2, 3)
INSERT T VALUES (0, 2, 1)

SELECT *,
ROW_NUMBER() OVER (ORDER BY 8) AS RowNumber,
RANK() OVER (ORDER BY 8) AS Rank,
DENSE_RANK() OVER (ORDER BY 8) AS DenseRank
FROM T

PK A B C RowNumber Rank DenseRank
----- ----- ----- ----- ---------- ---------- ----------
5 1 0 7 1 1 1
6 1 0 5 2 1 1
1 0 1 6 3 3 2
2 0 1 4 4 3 2
7 0 2 3 5 5 3
8 0 2 1 6 5 3
3 0 3 2 7 7 4
4 0 3 0 8 7 4

Notice how the ROW_NUMBER function ignores the duplicate values for column B
and assigns the unique integers from 1 to 8 to the 8 rows while the RANK
and DENSE_RANK functions assigns the same value to each of the pairs of duplicate rows.
Moreover, notice how the RANK function counts the duplicate rows even while
it assigns the same value to each duplicate row whereas the DENSE_RANK function
does not count the duplicate rows.

Paging with Rownumber

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
   SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
         ,OrderID
         ,OrderDate
         ,CustomerID
         ,EmployeeID
     FROM dbo.Orders
)

SELECT *
 FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
                 AND @PageNum * @PageSize
ORDER BY OrderDate
        ,OrderID;

Export to Excel in ASP.Net

Random rnd = new Random();
string filename = "SummaryReport" + rnd.Next(9999999).ToString() + ".xls";
HtmlForm form = new HtmlForm();
string attachment = "attachment;filename=" + filename;

//------------ Export to Excel from datagrid -----------------------------
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter stw = new StringWriter();
HtmlTextWriter htextw = new HtmlTextWriter(stw);

grdSearchResult.Columns[13].Visible = false;
form.Controls.Add(grdSearchResult);
this.Controls.Add(form);
form.RenderControl(htextw);
Response.Write(stw.ToString());
Response.End();

//----------------------------End---------------------------------------------

//---------------Export to excel from datatable ----------------------------

DataTable dt = dtAgentSearch;
//string attachment = "attachment; filename=Employee.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");

int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i <>
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();

//-----------------------------End---------------------------------------------