CODEWITHSUNDEEP
sqlsql-serversql-server-2008
sasikals26
sasikals26

Reputation: 865

DateTime format and calcaulting the difference in seconds in SQL query

Trying the below example on my learning process, can anyone please help me on below doubts,

  1. How to format the datetime column with specific date format
  2. How to find the time duration between execution start and stop in seconds

I have created the schema with below query,

    CREATE TABLE User (UserID int NOT NULL UNIQUE, Username Varchar(200), PRIMARY KEY (UserID));

    create table Process (ProcessID int NOT NULL UNIQUE, ProcessName varchar(100),LastModifiedDate datetime, LastModifiedByUser int not null UNIQUE, PRIMARY KEY (ProcessID),
    FOREIGN KEY (LastModifiedByUser) REFERENCES User(UserID));

    create table session (SessionID int NOT NULL UNIQUE, processID int NOT NULL UNIQUE , startdatetime datetime , stopdatetime datetime, startedbyuser int NOT NULL UNIQUE , PRIMARY KEY (sessionid) , FOREIGN KEY (processid) REFERENCES process(ProcessID) , FOREIGN KEY (startedbyuser) REFERENCES user (UserID));


    INSERT INTO User (UserID, Username)VALUES ('1', "XXX");
    INSERT INTO User (UserID, Username)VALUES ('2', "YYY");
    INSERT INTO User (UserID, Username)VALUES ('3', "ZZZ");

    INSERT INTO Process (ProcessID, ProcessName, LastModifiedDate,LastModifiedByUser) VALUES ('100', "running the job", CAST(N'2012-06-18 10:34:09.000' AS DateTime) , '1');
    INSERT INTO Process (ProcessID, ProcessName, LastModifiedDate,LastModifiedByUser) VALUES ('101', "Forced stopped the application", CAST(N'2012-06-18 10:34:09.000' AS DateTime) , '3');

    INSERT INTO session (SessionID, processID, startdatetime,stopdatetime , startedbyuser) values ('1001', '101',CAST(N'2012-06-18 12:34:09.000' AS DateTime ), CAST(N'2012-06-18 23:34:09.000' AS DateTime), '1');
    INSERT INTO session (SessionID, processID, startdatetime,stopdatetime , startedbyuser) values ('1002', '100',CAST(N'2012-06-18 15:34:09.000' AS DateTime ), CAST(N'2012-06-18 17:34:09.000' AS DateTime), '3');

And tried to retrieve the data,

SELECT Process.ProcessID, session.startedbyuser , session.startdatetime, session.stopdatetime , Process.LastModifiedDate, process.LastModifiedByUser FROM Process INNER JOIN session ON Process.ProcessID=session.ProcessID;

Upvotes: 0

Views: 50

Answers (1)

Mazhar
Mazhar

Reputation: 3837

How to format the datetime column with specific date format

You're on SQL 2008 so you've really only got CONVERT

How to find the time duration between execution start and stop in seconds

For this you have DATEDIFF

SELECT
    P.ProcessID
    ,S.startedbyuser
    ,[FormattedDateTime] = CONVERT(VARCHAR(30), S.startdatetime, 109)
    ,S.startdatetime
    ,S.stopdatetime
    ,P.LastModifiedDate
    ,Duration_in_Seconds = DATEDIFF(SECOND, S.startdatetime, S.stopdatetime)
    ,duration_as_time = CONVERT(TIME,stopdatetime-startdatetime) 
    ,P.LastModifiedByUser
FROM
    dbo.Process P
INNER JOIN  [dbo].[session] S ON P.ProcessID    = S.processID

Upvotes: 1

Related Questions