SQL - DATEDIFF with subquery
I need to pass the value downstream of the database SQL Server
, which is essentially the difference between the two timestamps
. It's easy enough with a function DATEDIFF
.
SELECT DATEDIFF(day, '2015-11-06 00:00:00.000','2015-12-25 00:00:00.000') AS DiffDate ;
However, while passing the value down, the code only knows the value of the order id, not the 2 timestamps shown above. So I need information timestamp
that should come from a subquery or whatever I think. The main nuts and bolts of the auxiliary request are here:
select O.DATE1 , C.DATE2
from TABLE1 O, TABLE2 C
WHERE O.VALUE1_ID = C.VALUE1
AND O.order_id = '12345678'
I tried several different ways, however none of them were sufficient. The last thing I'm tired of is below, which looks ok in terms of syntax, but I get the error:
Conversion error when converting date and / or time from character string
which I am never too sure how to handle 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.
source to share
Subqueries do not have to be strings, so remove the single quotes. Also, you say 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;
source to share
Could you please declare two datetime variables and then date both 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)
source to share
Could you try below? I used your additional query, but more information about the data in the tables "Table 1" and "Table 2" would be helpful
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 error when converting date and / or time from character string
is that you are passing strings (below) to the date function instead of using date
'(select O.VALUE1
from TABLE1 O
where O.VALUE1 = 16650476)'
source to share