Search code examples
sqlsql-serverdatetimetimesubtraction

How to subtract Time Column from DateTime Column in SQL?

I have Crea_Date column which is a DateTime Column. I want to subtract the value in the IST column from crea_date Column and return a new column with the DateTime Value in it. My sample data is like this:

States  crea_date               IST
AB      2014-12-30 15:01:00.000 12:30:00.0000000
AK      2014-12-29 16:32:00.000 10:30:00.0000000
AZ      2014-12-18 16:07:00.000 11:30:00.0000000

Thanks in Advance

Solution

  • If IST is an integer number of seconds:

    SELECT DATEADD(s, -IST, crea_date)
FROM yourTable

    If IST is of the TIME type:

    SELECT DATEADD(ms, DATEDIFF(ms, IST, '00:00:00'), crea_date)
FROM yourTable