Difference between DateTime and DateTime2 DataType

DateTime2 is the new Data Type introduced in Sql Server 2008 for storing Date

and Time value. As per MSDN, Microsoft Suggests to use this new Data Type 

for new work instead of DateTime.

Following table summarizes some of the major difference between this
new DateTime2 and the old DateTime Data Type.

	DateTime	DateTime2[(n)]
Min Value	1753-01-01 00:00:00	0001-01-01 00:00:00
Max Value	9999-12-31 23:59:59.997	9999-12-31 23:59:59.9999999
Storage Size	8 Bytes	6 to 8 bytes Note: Parameter n is optional and if it is not specified then fractional seconds precision is 7 digit and it can be from 0 to 7 digit. For fractional seconds precision <3, takes 6 bytes For fractional seconds precision 3 or 4 it will take 7 bytes For fractional seconds precision >4 it will take 8 bytes
Usage	Declare @now datetime	Declare @now datetime2(7)
Compliance	Is not an ANSI/ISO compliant	Is an ANSI/ISO compliant
Current Date and Time function	GetDate() – It returns DB Current Date and Time of DateTime Data Type Example: SELECT GETDATE() Result: 2011-09-16 13:23:18.767	SYSDATETIME()– It returns DB Current Date and Time of DateTime2 Data Type Example:SELECT SYSDATETIME() Result: 2011-09-16 13:23:18.7676720
+/- days	WORKS Example:  DECLARE @nowDateTimeDATETIME = GETDATE() SELECT@nowDateTime + 1 Result: 2011-09-17 13:44:31.247	FAILS – Need to use only DateAdd function Example:  DECLARE @nowDateTime2 DATETIME2= SYSDATETIME() SELECT @nowDateTime2+1 Result: Msg 206, Level 16, State 2, Line 2 Operand type clash: datetime2 is incompatible with int

Continue Reading

Multiple Pivots in Sql Server

CREATE TABLE TblPivot

(

Time_Dt Date,

Workflow_Id int not null identity(1,1),

Completed_No int,

Average Numeric(6,2)

)

insert into TblPivot

(Time_Dt,Completed_No,Average) values

('2016-08-24',null,1.5)

insert into TblPivot

(Time_Dt,Completed_No,Average) values

('2016-08-24',null,1.6)

insert into TblPivot

(Time_Dt,Completed_No,Average) values

('2016-08-24',null,2.6)

insert into TblPivot

(Time_Dt,Completed_No,Average) values

('2016-08-24',100,2.9)

Select Time_Dt,sum(Coalesce(([10004]),0)) Completed,

sum(Coalesce(([1]),0)) Workflow_1,

sum(Coalesce(([2]),0)) Workflow_2,

sum(Coalesce(([3]),0)) Workflow_3,

sum(Coalesce(([4]),0)) Workflow_4

from 

(SELECT

*

 FROM

 (

    SELECT

Time_Dt,Workflow_Id,

case when Completed_No is not null then 10000+Workflow_Id end Count_Workflow_Id,

Completed_No,Average

 FROM TblPivot

 ) AS P

 

PIVOT

(

  Sum(Completed_No) FOR Count_Workflow_Id IN ([10004])

) AS pv1

 

-- For ItemName

PIVOT

(

 Sum(Average) FOR  Workflow_Id in ([1], [2], [3],[4])

) AS pv2 )a group by Time_Dt

--

Regards,

Ashif Ahamed 

 

"We can't solve problems by using the same kind of thinking we used when we created them - Albert Einstein"

Continue Reading

Base Query to Date Difference Between Overlapping and Non-Overlapping Dates

CREATE TABLE dbo.Projects

( projectid  INT          NOT NULL,

  title      VARCHAR(100) NOT NULL,

  start_date DATE         NOT NULL,

  end_date   DATE         NOT NULL);

Go

INSERT INTO dbo.Projects(projectid, title, start_date, end_date) VALUES

  (1, 'Project 1', '20100212', '20100220'),

  (2, 'Project 2', '20100214', '20100312'),

  (3, 'Project 3', '20100124', '20100201'),

  (4, 'Project 4', '20100401', '20100410'),

  (5, 'Project 5', '20100420', '20100425'),

  (6, 'Project 6', '20100501', '20100509'),

  (7, 'Project 7', '20100409', '20100415'),

  (8, 'Project 8', '20100414', '20100421'),

  (9, 'Project 9', '20100605', '20100610'),

  (10, 'Project 10', '20100614', '20100621'),

  (11, 'Project 11', '20100601', '20100625');

Go

והשליפה הרקורסיבית:

With T1 As

(Select    Row_Number() Over(Order By Start_date) N,

        *

From    Projects),

T2 As

(Select 1 N,

        start_date,

        end_date

From    T1

Where    N=1

Union All

Select    T2.N+1,

        T2.start_date,

        Case When T1.end_date>=T2.end_date Then T1.end_date Else T2.end_date End

From    T1

Inner Join T2

        On T2.End_date >= T1.start_date

Where    T1.N=T2.N+1

Union All

Select    T2.N+1,

        T1.start_date,

        T1.end_date

From    T1

Inner Join T2

        On T2.End_date < T1.start_date

Where    T1.N=T2.N+1

)

Select    start_date,

        Max(end_date) end_date

From    T2

Group By start_date;

הפלט המתקבל:

start_date    end_date

2010-01-24    2010-02-01

2010-02-12    2010-03-12

2010-04-01    2010-04-25

2010-05-01    2010-05-09

2010-06-01    2010-06-25

Continue Reading