Is this a valid substring code

HI

I'am using the following code for my report :

len(replace(substring(FC.effectivitycalendar,((((datepart(dw,FI.followupby))-2)*24)+(datepart(hour,FI.followupby))),((168-((((datepart(dw,FI.followupby))-2)*24)+(datepart(hour,FI.modifiedon))))+1),'-',' '))))+
len(replace(substring(FC.effectivitycalendar,1, ((((datepart(dw,FI.followupby))-2)*24)+(datepart(hour,FI.modifiedon))))))+
len(replace(substring(FC.effectivitycalendar,1,168)))*((datediff(ww,FI.followupby,FI.modifiedon))-1)

but when try this I get the following error message:

Msg 174, Level 15, State 1, Line 8
The substring function requires 3 argument(s).
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'datepart'.

Could someone help me on this?

Many Thanks

Avecinna

This looks like a disaster waiting to happen...

Tell us what you are attempting to accomplish. Perhaps there are other ways to get to your desired goal.

What is the starting date, and what kind of a date should you end up with?

|||

All because of the unblanced brackets.. Here I somewhat managed to correct this...

len(replace(substring(FC.effectivitycalendar,((datepart(dw,FI.followupby)-2)*24)+(datepart(hour,FI.followupby)),((168-((((datepart(dw,FI.followupby))-2)*24) +(datepart(hour,FI.modifiedon))))+1)),'-',' ')) +

len(replace(substring(FC.effectivitycalendar,1, ((((datepart(dw,FI.followupby))-2)*24)+(datepart(hour,FI.modifiedon)))),'-',' '))+

len(replace(substring(FC.effectivitycalendar,1,168),'-',' '))*((datediff(ww,FI.followupby,FI.modifiedon))-1)

|||

Hi everybody

Arnie...thanks for replying, I wil explain the target behind this al

I must calculate the exact overdue time of a service call........but without the time that the service company is closed.

I can infer this from a entity named effectivitycalendar: this entity exists of a long string of + and - signs

like this: --++++++-++++

This string contains exactly 168 characters that means 7 day a week.........the plus signs means that the service company is open.

So I have the deadline (followupby) and the time that the service call has been closed (modifiedon) and

There are also calls which are still open.....

some calls can be modified within a week others will take more weeks and so on...

Manivannan again many thanks....for your help!! I will trie your solution

greetings

Avecinna

|||

I suspected you were working with some form of positional manipulation. That is a 'ugly' thing to have to live with and support.

Impossible to handle any service company that opens from 8:30-5:00 -you have them either as 8-5 or 9-5, both of which are inaccurate.

A MUCH easier way to handle such problems, and much easier to manage and manipulate, is to use a 'Calendar' table. Here is an article that will give you good information about how to create and use a Calendar table.

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||Yes. I agree with Arnie.|||

HI mannivannan and Arnie

I agree with you Arnie, but for now I'am close to the deadline some I finish this despite of the method I use.

I still have some problems with some code maybe you could help me on this cause I can't see what's wrong, I think it's again the unbalanced brackets...

else

len(replace(substring(fc.effectivitycalendar, ((datepart(dw,FI.followupby)-2)*24)+(datepart(hour,FI.followupby)),((datepart(dw,FI.modifiedon)-2)*24)+ datepart(hour,FI.modifiedon)-(((datepart(dw,FI.followupby)-2)*24)+(datepart(hour,FI.followupby)))+1)),'-',' ')) end

Many thanks

Avecinna

|||

Since it is missing parentheses, perhaps if you deconstructed the expression, and traced the left-right pairs, you would find the missing one. (By the way, that is exactly what one of us would do to find it. Imagine the satisfaction from finding it yourself...)

A nice thing about SSMS is that is helps find the right paren to a left paren. (Eventually you will discover that you have one too many right parens. Oh, but where to remove one...)