TSQL query to split full name into firstname and lastname

This script only works with combination of firstname+' '+lastname.
--firstname
SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) as firstname
--lastname
SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, LEN(fullname)) as lastname

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