CODEWITHSUNDEEP
sqlsql-serverdatediff
pegzy
pegzy

Reputation: 33

SQL - DATEDIFF with a subquery

I need to pass a value downstream from a SQL Server DB which is essentially the difference between two timestamps. This is simple enough with the DATEDIFF function. 

SELECT DATEDIFF(day, '2015-11-06 00:00:00.000','2015-12-25 00:00:00.000') AS DiffDate ;

However at the time of passing the value down the code only knows an order ID value and not the 2 time stamps shown above. Therefore I need the timestamp info to come from a subquery, or something else I think. The main nuts and bolts of the sub query is here:

select O.DATE1 , C.DATE2
from TABLE1 O, TABLE2 C
WHERE O.VALUE1_ID = C.VALUE1
AND O.order_id = '12345678'

I've tried a few different ways , however none have been sucesfull. The latest I've tired is below, which from a syntax perspective looks ok, but I get the error:

Conversion failed when converting date and/or time from character string

which I'm never too sure how to cope or deal with. 

select DATEDIFF (day,'(select O.VALUE1 
from TABLE1 O 
where O.VALUE1 = 16650476)' , 
               '(SELECT C.VALUE1 
                 from TABLE1 O, TABLE2 C 
                 WHERE O.VALUE1 = C.VALUE2 AND O.order_id = 12345678)') AS DIFFDATE;

Any pointers or help would be appreciated.

Upvotes: 3

Views: 8220

Answers (4)

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

The subqueries shouldn't be strings, so remove the single quotes. Also, you talk abot O.DATE1 and C.DATE2, so you probably mean something like this:

select DATEDIFF (day, 
              (select O.DATE1 
                 from TABLE1 O 
                where O.VALUE1 = 16650476) , 
              (SELECT C.DATE2 
                from TABLE1 O, TABLE2 C 
                WHERE O.VALUE1 = C.VALUE2 AND O.order_id = 12345678)) AS DIFFDATE;

Upvotes: 3

Luke Franklin
Luke Franklin

Reputation: 355

Could you try the below I have used your sub query but more information on the data in the tables "Table1" and "Table2" would be useful

SELECT
    DATEDIFF(day, D.Date1, D.Date2) AS DiffDate 
FROM
(
    select O.DATE1 as Date1 , C.DATE2 as Date2
    from TABLE1 O, TABLE2 C
    WHERE O.VALUE1_ID = C.VALUE1
    AND O.order_id = '12345678' 
) D

The reason you are getting the error

Conversion failed when converting date and/or time from character string

is because you are passing strings (below) to the datediff function instead of using a date

'(select O.VALUE1 
from TABLE1 O 
where O.VALUE1 = 16650476)'

Upvotes: 0

BigDevJames
BigDevJames

Reputation: 2674

Could you Declare two variables of datetime and then datediff the two variables? 

DECLARE @FirstDate AS datetime
DECLARE @SecondDate AS datetime
SET @FirstDate = (select O.VALUE1 from TABLE1 O where O.VALUE1 = 16650476)
SET @SecondDate = (SELECT C.VALUE1 from TABLE1 O, TABLE2 C WHERE O.VALUE1 = C.VALUE2 AND O.order_id = 12345678)
SELECT DATEDIFF(day, @FirstDate, @SecondDate)

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

I think you want the following:

SELECT DATEDIFF(DAY, cj.SomeDateColumn, C.SomeDateColumn)
FROM TABLE1 O 
JOIN  TABLE2 C  ON O.VALUE1 = C.VALUE2
CROSS JOIN (SELECT O.SomeDateColumn FROM TABLE1 WHERE order_id = 16650476) cj
WHERE  O.order_id = 12345678

Upvotes: 0

Related Questions