When I execute the stored procedure below I get the following error:
Server: Msg 241, Level 16, State 1, Procedure aa_dch_ChgDOS_To_ClmCreate_LagTime, Line 19
Syntax error converting datetime from character string.
I'm sure it has to do with this bit of code that I used from another stored procedure we already had - and I don't understand it all.
DECLARE @StartDate DATETIME
DECLARE @strStartDate varchar(28)
DECLARE @EndDate DATETIME
select @EndDate = max(create_timestamp),
@strStartDate = substring(convert(varchar(28), max(create_timestamp), 102),1,6) + '01'
from claims
SET @StartDate = CONVERT(datetime, @strStartDate)
What I want to do is get the most recent - max - date and use that to get the first of that month. The other procedure it is used in gets the max date from a varchar date. This is a timestamp and I don't know how to change it so it will work. I thought maybe it was the Set statement but if I delete that then I end up with an empty table.
Also, I want to put the date (create_timestamp) in the table but I think I've read on this site that I can't create a timestamp field for it - but is datetime ok?
Thank you -
Server: Msg 241, Level 16, State 1, Procedure aa_dch_ChgDOS_To_ClmCreate_LagTime, Line 19
Syntax error converting datetime from character string.
I'm sure it has to do with this bit of code that I used from another stored procedure we already had - and I don't understand it all.
DECLARE @StartDate DATETIME
DECLARE @strStartDate varchar(28)
DECLARE @EndDate DATETIME
select @EndDate = max(create_timestamp),
@strStartDate = substring(convert(varchar(28), max(create_timestamp), 102),1,6) + '01'
from claims
SET @StartDate = CONVERT(datetime, @strStartDate)
What I want to do is get the most recent - max - date and use that to get the first of that month. The other procedure it is used in gets the max date from a varchar date. This is a timestamp and I don't know how to change it so it will work. I thought maybe it was the Set statement but if I delete that then I end up with an empty table.
Also, I want to put the date (create_timestamp) in the table but I think I've read on this site that I can't create a timestamp field for it - but is datetime ok?
Thank you -
Code:
ALTER proc dbo.aa_dch_ChgDOS_To_ClmCreate_LagTime
as
--For chgDOS to ClmCreate Lag Time srb 3-14-06
/* Convert date strings to dates */
DECLARE @StartDate DATETIME
DECLARE @strStartDate varchar(28)
DECLARE @EndDate DATETIME
DROP TABLE CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime
select @EndDate = max(create_timestamp),
@strStartDate = substring(convert(varchar(28), max(create_timestamp), 102),1,6) + '01'
from claims
SET @StartDate = CONVERT(datetime, @strStartDate)
create table CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime
(enc_nbr numeric(9) not null,
transmitter_id varchar(80) null,
payer_name varchar(40) not null,
service_from_date varchar(8) not null,
create_timestamp datetime null,
ChgDosClmCreate numeric(10)
)
Insert into CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime
(enc_nbr,
transmitter_id,
payer_name,
service_from_date,
create_timestamp,
ChgDosClmCreate
)
SELECT
distinct
pe.enc_nbr,
pp.transmitter_id,
pm.payer_name,
cd.service_from_date,
c.create_timestamp as ClaimCreate,
datediff(dd, cd.service_from_date, c.create_timestamp)-- as [ChgDOS-ClmProc]
FROM
claim_requests cr
inner join claims c on c.claim_id = cr.claim_id
inner join claim_detail cd on c.claim_id = cd.claim_id
inner join claim_charges cc on (cc.claim_id = c.claim_id and cc.enc_id = c.enc_id)
--inner join charges chg on (chg.source_id = c.enc_id and cc.charge_id = chg.charge_id and cd.line_charges = chg.amt)
inner join patient_encounter pe on c.enc_id = pe.enc_id
left outer join practice_payers pp on pp.payer_id = c.payer_id
left outer join payer_mstr pm on c.payer_id = pm.payer_id
WHERE
pp.transmitter_id in ('00900', '00156', '87726', '62308', '60054')
and c.cob = 1
and c.create_timestamp between @StartDate and @EndDate
and datediff(dd, cd.service_from_date, c.create_timestamp)<> 0
order by pp.transmitter_id, pe.enc_nbr
Select Count(*)
From CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime
SELECT
distinct
enc_nbr,
transmitter_id,
payer_name,
service_from_date,
create_timestamp as ClaimCreate,
ChgDosClmCreate
From
CRMonthly.dbo.aa_tbl_ChgDOS_To_ClmCreate_LagTime