Subtract date from day field

Question

Original Question

I need to write a Microsoft SQL statement where I subtract a date from number of days from another table. So, essentially, the table example is below:

Table 1 (date)           Table 2 (number of days)
2013-05-08 23:59:13.000     7
2013-05-08 23:59:16.000     7
2013-05-08 23:59:06.000     7

Any help would be appreciated.

Updated Question

The only two item I have to work with is the following information:

enter image description here

I need to know how long an invoice has been in the invoice queue. TimeoutDate is when the invoice will timeout of the queue, and TimeOutDays is the length of days this queue is given before it times out, but it's business days, so excluding weekends.

So, if you calculate based on this information, the invoice has been in the queue for 8 business days <-- this is the answer I need to get from the query.

Extra Information

The invoice entered into the system 04/30/2013, but since the webapp does not count the day the invoice entered the system, the time-out counter starts the day after, which is 05/1/2013. Hope this explains a little clearer!

I have a quick query, but that's not going to help, since it doesn't show what I need, but in effect below is the results of what I need:

enter image description here

You can use DATEADD Function, http://msdn.microsoft.com/en-us/library/ms186819.aspx. However, how are you going to join those 2 tables? — EricZ, May 08 '13 at 01:50

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

Try

SELECT DATEADD(day, -(t2.days), t1.datefield) new_date
  FROM table1 t1 JOIN
       table2 t2 ON t1.id = t2.id

Output:

|                   NEW_DATE |
------------------------------
| May, 01 2013 23:59:13+0000 |
| May, 01 2013 23:59:16+0000 |
| May, 01 2013 23:59:06+0000 |

SQLFiddle

UPDATE: A solution to the completely changed question

SELECT timeoutdate,
       timeoutdays,
       timeoutdays
       -((DATEDIFF(dd, GETDATE(), timeoutdate) + 1)
       -(DATEDIFF(wk, GETDATE(), timeoutdate) * 2)
       -(CASE WHEN DATENAME(dw, GETDATE()) = 'Sunday' THEN 1 ELSE 0 END)
       -(CASE WHEN DATENAME(dw, GETDATE()) = 'Saturday' THEN 1 ELSE 0 END)) daysinqueue
FROM table1

Output:

|             TIMEOUTDATE | TIMEOUTDAYS | DAYSINQUEUE |
-------------------------------------------------------
| 2013-05-14 23:59:13.000 |          10 |           9 |

SQLFiddle

Thanks @CMS for a solution for getting number of working days between two dates

edited May 23 '17 at 11:50

Community

1
1

answered May 08 '13 at 02:03

peterm

91,357
15
148
157

Thank you both, I'll try, and let you know how it went! – Anon May 08 '13 at 18:21
@Anon Did it help? Do you need more help on your question? – peterm May 10 '13 at 00:13
Sorry, I haven't checked it out yet..but going to check it out soon. Will let you know, thanks for the follow-up – Anon May 10 '13 at 22:49
Yes peterm, I need more help! I just put my question up..and it's little more difficult. – Anon May 11 '13 at 00:43
@Anon First of all, you better post your additional information in your original question. Just use edit link for that. And delete posted answer. Secondly your explanation is very confusing and doesn't correlate to the sample data you provided. Can you post source sample data and desired result **based on that data** in a tabular form? – peterm May 11 '13 at 00:57
OMGosh! Thanks it works perfectly..and very efficiently! I defintaely wouldn't have got it working without you. Thanks PeterM! – Anon May 13 '13 at 18:29

Sachin · Answer 2 · 2013-05-08T02:13:10.387

0

You can try something like this

Select DATEADD(month, -(Table2.day) , Table1.date) 
FROM Table1 Inner Join Table2
ON Table1.Col=Table2.Col

Of-course you can modified the join as per your structure.

edited May 08 '13 at 02:13

answered May 08 '13 at 01:54

Sachin

40,216
7
90
102

Subtract date from day field

2 Answers2