CODEWITHSUNDEEP
sqlsql-server
RJM
RJM

Reputation: 311

Time difference between two varchar datetime

I want to use the datediff function to get the time difference in seconds between two date time values which are in the varchar format. Following is my query which is giving me the error.

DATEDIFF(second, CONVERT(Datetime, data_deviceTransactions_createdTime, 120), CONVERT(Datetime, data_deviceTransactions_updatedTime, 120))

data_deviceTransactions_createdTime and data_deviceTransactions_updatedTime are varchar values,

Upvotes: 0

Views: 744

Answers (1)

Fahmi
Fahmi

Reputation: 37473

Try this: 

CONVERT(DATETIME, SUBSTRING('Thu, 20 Sep 2018 05:54:30 GMT', 5, LEN('Thu, 20 Sep 2018 05:54:30 GMT') - 8), 113)

This should return 2018-09-20 05:54:30.000

SELECT
    DATEDIFF(second, CONVERT(DATETIME, SUBSTRING(data_deviceTransactions_createdTime, 5, LEN(data_deviceTransactions_createdTime) - 8), 113), CONVERT(DATETIME, SUBSTRING(data_deviceTransactions_updatedTime, 5, LEN(data_deviceTransactions_updatedTime) - 8), 113))

Upvotes: 3

Related Questions