Quantcast

Dec 18, 2008

SQL Datetime Difference SAMPLE title

Step 1:Create a table for holding the datas of gantt chart.
Step 2:Create a trigger and call functions to update the columns

DURATION_HOURS, DURATION_MINUTES and DURATION_SECONDS from functions
Step 3:Create a function and use SQL datediff function to calculate the

date part

CREATE TABLE GANT_CHART
(
BATCH_NO VARCHAR(150) PRIMARY KEY,
RESOURCE_NAME VARCHAR(200),
START_TIME DATETIME,
END_TIME DATETIME,
DURATION_HOURS INT,
DURATION_MINUTES INT,
DURATION_SECONDS INT
)


CREATE TRIGGER DUR_CALC
ON WHBS_GANTT
FOR INSERT
AS
UPDATE GANT_CHART SET DURATION_HOURS = DBO.DATE_DIFF_HRS(START_TIME,END_TIME)
UPDATE GANT_CHART SET DURATION_MINUTES = DBO.DATE_DIFF_MIN(START_TIME,END_TIME)
UPDATE GANT_CHART SET DURATION_SECONDS = DBO.DATE_DIFF_SECS(START_TIME,END_TIME)


/*****************TIME DIFFERENCE HOURS FUNCTION**********************/
CREATE FUNCTION DATE_DIFF_HRS
(
@DATE1 DATETIME,
@DATE2 DATETIME
)
RETURNS INT
AS
BEGIN
RETURN CONVERT(INT,DATEDIFF(HOUR,@DATE1 ,@DATE2))
END

--DROP FUNCTION DATE_DIFF_HRS


/*****************TIME DIFFERENCE MINUTES FUNCTION**********************/
CREATE FUNCTION DATE_DIFF_MIN
(
@DATE1 DATETIME,
@DATE2 DATETIME
)
RETURNS INT
AS
BEGIN
RETURN CONVERT(INT,DATEDIFF(MINUTE,@DATE1 ,@DATE2)%60)
END

--DROP FUNCTION DATE_DIFF_MIN


/*****************TIME DIFFERENCE SECONDS FUNCTION**********************/
CREATE FUNCTION DATE_DIFF_SECS
(
@DATE1 DATETIME,
@DATE2 DATETIME
)
RETURNS INT
AS
BEGIN
RETURN CONVERT(INT,DATEDIFF(SECOND,@DATE1 ,@DATE2)%60)
END

1 comment: