April 3, 2008 at 3:14 pm
Hi Experts,
I have the following syntax within a cursor - (getdate() - getutcdate()) as date_Time,
However it is not returning the correct amount of records...which I know that it should.
Can anyone point me in the right direction?
Thanx
Steve
April 3, 2008 at 3:19 pm
SteveH2455 (4/3/2008)
...Can anyone point me in the right direction?...
Not with the information you provided.
April 3, 2008 at 4:15 pm
I've enclosed the complete script (Cursor)....
declare
@Sysid integer,
@net_user varchar(24),
@user_sysid int,
@cv_user varchar(12),
@CV_docnum int,
@dm_docnum int,
@datetime datetime,
@cv_action varchar(24),
@activity int,
@cvuser_sysid int,
@activity_desc varchar(70)
Declare activity cursor for
select *, case Action
when 'Edit' then 2
when 'Updated' then 20
when 'View' then 19
else 0
end as Activity
from openquery([nt036\live],'select
a.acr_doc_id as docnumber,
cast(a.acr_action_date as datetime)+'' ''+
case len(cast(a.acr_action_time as varchar))
when 3 then substring(''0''+cast(a.acr_action_time as varchar),1,2)
+ '':'' + substring(''0''+cast(a.acr_action_time as varchar),3,4)
else substring(cast(a.acr_action_time as varchar),1,2) + '':''
+ substring(cast(a.acr_action_time as varchar),3,2)
end - (getdate()- getutcdate()) as date_Time,
rtrim(us.cu_network_login) as net_user,
a.acr_login as cv_user,
isnull(a.acr_command_prompt,
case a.acr_action
when ''U'' then ''Updated''
When ''L'' then ''Replied''
when ''F'' then ''F''
else a.acr_action
end) as Action
from dbo.us_action_record a
left outer join dbo.cv_user us on us.cu_logname = a.acr_login
where a.acr_doc_id = 283841')
where
action in ('Edit','Updated','Finalise','Replied')
or
(action = 'View' and date_time > getdate()-365)
Select @cvuser_sysid = system_id from people where user_id = 'CV'
OPEN activity
FETCH next FROM activity
into @cv_docnum, @datetime, @net_user, @cv_user, @cv_action, @activity
WHILE @@FETCH_STATUS = 0
Begin
select @dm_docnum = null
Select @user_sysid = null
Select @activity_desc = @cv_action
select@dm_docnum = docnumber from profile
where tt_oldref_id = cast(@cv_docnum as varchar)
Select @user_sysid = system_id from people where user_id = @net_user
if isnull(@user_sysid,'') not in (select system_id from people)
Begin
Select @user_sysid = @cvuser_sysid
Select @activity = 0
Select @activity_desc = rtrim(@cv_action) +' - '+upper(@cv_user)
end
if @dm_docnum is not null
Begin
exec docsadm.TT_sp_nextkey 'SYSTEMKEY', @Sysid OUTPUT
INSERT INTO DOCSADM.ACTIVITYLOG
(SYSTEM_ID,
DOCNUMBER,
VERSION_LABEL,
APPLICATION,
REF_LIBRARY,
REF_DOCUMENT,
ACTIVITY_TYPE,
START_DATE,
AUTHOR,
TYPIST,
ELAPSED_TIME,
TYPE_TIME,
KEYSTROKES,
PAGES,
BILLABLE,
BILLED_ON,
ACTIVITY_POSTED,
CR_IN_USE,
ACTIVITY_DESC )
VALUES (
@Sysid,
@dm_docnum,
'1',
0,
-1,
0,
@activity,
@datetime,
@user_sysid,
@user_sysid,
0,
1,
0,
0,
NULL,
@datetime,
NULL,
NULL,
@activity_desc)
end
FETCH next FROM activity
into @cv_docnum, @datetime, @net_user, @cv_user, @cv_action, @activity
end
close activity
deallocate activity
April 4, 2008 at 12:58 am
What is it doing and what should it be doing?
Some schema definitions, sample data and expected output would be useful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2008 at 6:26 am
SteveH2455 (4/3/2008)
I have the following syntax within a cursor - (getdate() - getutcdate()) as date_Time,
Have you looked at what this returns...
select getdate()
select getutcdate()
select getdate() - getutcdate()
Execute that in Query Analyzer or where ever you write your code...this may answer your question.
(Edited tags)
If it was easy, everybody would be doing it!;)
April 5, 2008 at 7:50 pm
Hi...after some further investigation if I remove the following from the script then I can retrieve the correct number of records - can you see anything that is wrong with the section of the script?
end as Activity
from openquery([nt036\live],'select
a.acr_doc_id as docnumber,
cast(a.acr_action_date as datetime)+'' ''+
case len(cast(a.acr_action_time as varchar))
when 3 then substring(''0''+cast(a.acr_action_time as varchar),1,2)
+ '':'' + substring(''0''+cast(a.acr_action_time as varchar),3,4)
else substring(cast(a.acr_action_time as varchar),1,2) + '':''
+ substring(cast(a.acr_action_time as varchar),3,2)
end - (getdate()- getutcdate()) as date_Time,
rtrim(us.cu_network_login) as net_user,
a.acr_login as cv_user,
isnull(a.acr_command_prompt,
case a.acr_action
when ''U'' then ''Updated''
When ''L'' then ''Replied''
when ''F'' then ''F''
else a.acr_action
end) as Action
from dbo.us_action_record a
left outer join dbo.cv_user us on us.cu_logname = a.acr_login
where a.acr_doc_id = 283841')
Altho the script runs ok it's not returning the correct amount of records.
Thanx
Steve
April 7, 2008 at 6:03 am
What is it about the data (records) that are being returned is not correct?
Are you getting too many records, too few records, none at all?
Start with your embedded query (I simplified it for clarity)...
select *
from dbo.us_action_record a
left outer join dbo.cv_user us on us.cu_logname = a.acr_login
where a.acr_doc_id = 283841
...and run this directly and see what you are getting. Compare that to what you think you are supposed to be getting, then look in the tables to make sure you have the data in there you are supposed to be getting.
The 1st place to look when not getting data you expected is to make sure the data actually exists.
Since you said the output from the above query is being used in a cursor, then if the above is working correctly, then look at 'getdate()- getutcdate()'.
On my machine,
select getdate()- getutcdate()
returns '1899-12-31 20:00:00.000'
I don't know what you are doing, but I see this value is being subtracted from some other values. I'm just guessing here, but it appears you actually want the difference in hours. If so, they try 'datediff(hh, getdate(), getutcdate())'. See also SQL Server Books Online (BOL) for lots of wonderful info about 'datediff' and other datetime functions.
If none of this works, then you will need to be more specific in what you are expecting from your query in order for anyone to be or more help.
If it was easy, everybody would be doing it!;)
April 8, 2008 at 12:11 pm
select getdate()-getutcdate() will not impact the number of records. It is just going to return a time, or as I suspect you expect it to return an offset to calculate the timezone.
Not sure, but expect for a couple of times per year (assuming DST) is used, the offset doesn't change enough to calculate it RBAR.
However, if you are trying to calculate the TZ of the source of the records, it won't be doing that. The calculate above is only valid for your connection, not the data in the DB.
Also, you are using the calcs like the following
getdate()-365.
A better approach would be to use:
SELECT DATEADD(yy,-1,GETDATE())
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply