got it to work , as u have said, datepart works like a charm, manage to get mth and week out of it. however,...

YESSSSS, i actually solved it....the 1st part at least. tore the whole query down and started from each query and slowly put the joins in and it has worked! phew..but...

SELECT  svd.call_req.status, svd.call_req.description, svd.call_req.summary,

svd.ctct.c_last_name, svd.ctct.c_first_name, svd.int_org.iorg_name, DATEADD(ss, svd.call_req.open_date, '1970-01-01') AS open_date, DATEADD(ss,svd.call_req.close_date, '1970-01-01') AS close_date, svd.prob_ctg.sym, int_org_1.iorg_name ,ctct_2.c_last_name, 

int_org_2.iorg_name,ctct_3.c_last_name,ctct_1.c_last_name

FROM     svd.call_req INNER JOIN

 svd.prob_ctg ON svd.call_req.category = svd.prob_ctg.persid INNER JOIN

       svd.ctct AS ctct_1 ON svd.call_req.customer = ctct_1.id...

sigh why do i get this?

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name 'call_req'.

1st line is: SELECT call_req.status, call_req.description, call_req.summary,

call_req is the table name, while status, description and...

what abt this question?

ohhhhh u don't have to include the database name in the alias, duh! same as the sub-query...

alright thanks, have to try it out, hopefully it works..

SELECT     call_req.ref_num, call_req.status, ctct_1.c_last_name, call_req.summary, 

DATEADD(ss, call_req.open_date, '1970-01-01') AS [open_date], DATEADD(ss, call_req.close_date, '1970-01-01') AS [close_date], DATEADD(ss, call_req.open_date, '1970-01') AS [Monthlogged], prob_ctg.sym, 

call_req.description, int_org.iorg_name, ctct.c_first_name, ctct.c_last_name,

int_org_1.iorg_name ,ctct_2.c_last_name, call_req.status, act_log.description AS activity_description, int_org_2.iorg_name,ctct_3.c_last_name, DATEADD(ss, call_req.open_date, '1970-01')...

a separate question:

i use SELECT DATEADD(ss, table & column name, '1970-1-1') to convert a LOCAL_TIME value to yr date and time. What if i only want the output in the...

ah yes, i seemed to have missed out an alias for the sub-query. and should the sub-query be in [ square brackets or ( round brackets?

i've posted in another forumn,...