Advertisement

07.31.2008 at 11:11AM PDT, ID: 23611860
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.0

Converting varchar to datetime

Asked by Gallitin in SQL Query Syntax

Tags: , ,

I keep getting the error:

Server: Msg 241, Level 16, State 1, Line 152
Syntax error converting datetime from character string.Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:

--Table variables
declare @PatientID	varchar(255)
declare @RSPLastName 		varchar(255)
declare @RSPFirstName 	varchar(255)
declare @RSPAddress1 		varchar(255)
declare @RSPAddress2 		varchar(255)
declare @RSPCity 			varchar(255)
declare @RSPState 			varchar(255)
declare @RSPZip 			varchar(255)
declare @InvoiceNo 	varchar(255)
declare @RSPPhone			varchar(255)
declare @DocDate 		datetime
declare @DOS 			datetime
declare @Amount 		money
declare @PatientLastName 	varchar(255)
declare @PatientFirstName 	varchar(255)
declare @RSPDOB 	datetime
declare @RSPSSN		char(9)
 
--Local varaibles
declare @DebtorID 	int
declare @ServiceID 	int
declare @Today 		datetime
declare @OrgID 		int
declare @AccountID 	int
declare @SeriesID 	int
declare @AddressID 	int
declare @NewServiceID 	int
declare @EventDesc 	varchar(255)
declare @Count 		int
declare @Balance 	money
declare @Desc 		varchar(255)
 
--Initialize variables
set @Today = getdate()
set @OrgID = 2525
set @AccountID = 137
set @SeriesID = 1
set @Count = 0
set @Balance = 0
 
declare C1 cursor static for
select 	PatientID,
	case when ltrim(rtrim(RSPLastName)) is null then PatientLastName else ltrim(rtrim(RSPLastName)) end as LastName,
	case when ltrim(rtrim(RSPLastName)) is null then PatientFirstName else ltrim(rtrim(RSPFirstName)) end as FirstName,
	case when ltrim(rtrim(RSPLastName)) <> '' and ltrim(rtrim(RSPAddress1)) <> '' then
		RSPAddress1
	else
		RSPAddress1
	end as Address1,
	case when ltrim(rtrim(RSPLastname)) <> '' and ltrim(rtrim(RSPAddress1)) <> '' then
		RSPAddress2
	else
		RSPAddress2
	end as Address2,
	case when ltrim(rtrim(RSPLastName)) <> '' and ltrim(rtrim(RSPAddress1)) <> '' then
		RSPCity
	else
		RSPCity
	end as City,
	case when ltrim(rtrim(RSPLastName)) <> '' and ltrim(rtrim(RSPAddress1)) <> '' then
		RSPState
	else
		RSPState
	end as State,
	case when ltrim(rtrim(RSPLastName)) <> '' and ltrim(rtrim(RSPAddress1)) <> '' then
		RSPZip
	else
		RSPZip
	end as Zip,
	J.InvoiceNo,
	case when ltrim(rtrim(RSPLastName)) <> '' and ltrim(rtrim(RSPAddress1)) <> '' then
		RSPPhone
	else
		RSPPhone
	end as Phone,
	(convert(datetime,ltrim(rtrim(isnull(DocDate,101))))) as DocDate,
	DOS as DOS,
	sum(convert(money,ltrim(rtrim(isnull(Amount,0))))) as Amount,
	PatientLastName,
	PatientFirstName,
	RSPDOB as DOB,
	RSPSSN as SSN
 
from StrategicAR_Customers.dbo.Customer_Juros J
 
left outer join 
(
	select InvoiceNo, AccountNo
	from Debtor D
	inner join DebtorService DS on D.DebtorID = DS.DebtorID
	where OrgID = 2525
) E on E.AccountNo = J.PatientID and E.InvoiceNo = J.InvoiceNo
where PatientID not like '%CASH%'
and J.InvoiceNo not like '%A999999%'
and J.InvoiceNo != ''		-- This filters out any records with blank invoice numbers.
and E.InvoiceNo is null  
 
and PatientID not in
(
	select PatientID
	from StrategicAR_Customers.dbo.Customer_Juros
	group by PatientID, InvoiceNo
	having 	sum(convert(money,ltrim(rtrim(isnull(Amount,0))))) < 0
	-- 2007-12-19 :: Removed the 60-day rule for EXCLUSIONS by replacing the following statement by the one above
 
	-- having min(dbo.F_FixDate(DocDate)) < dateadd(d,-60,getdate()) and sum(cast(Amount as money)) < 0
)
 
and J.InvoiceNo not in
(
	select InvoiceNo
	from StrategicAR_Customers.dbo.Customer_Juros
	group by PatientID, InvoiceNo
	having 	sum(convert(money,ltrim(rtrim(isnull(Amount,0))))) = 0
)
group by	PatientID,
		RSPLastName,
		RSPFirstName,
		PatientLastName,
		PatientFirstName,
		RSPAddress1,
		RSPAddress2,
		RSPCity,
		RSPState,
		RSPZip,
		RSPPhone,
		J.InvoiceNo,
		DocDate,	
		DOS,
		RSPDOB,
		RSPSSN
 
 
having 	(convert(datetime,ltrim(rtrim(isnull(DocDate,101))))) < dateadd(d,-60,getdate()) and sum(convert(money,ltrim(rtrim(isnull(Amount,0))))) > 0
 
 
OPEN C1
FETCH NEXT FROM C1
INTO 	@PatientID, @RSPLastName, @RSPFirstName, @RSPAddress1, @RSPAddress2, @RSPCity, @RSPState, @RSPZip, @InvoiceNo, @RSPPhone, @DocDate, 
     	@DOS, @Amount, @PatientLastName, @PatientFirstName, @RSPDOB, @RSPSSN
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
	--clear the DebtorID
	set @DebtorID = null
	
	--Does the debtor already exist in an acceptable status?
	select @DebtorID = DebtorID
	from Debtor
	where OrgID = @OrgID
	and ltrim(rtrim(AccountNo)) = @PatientID
	and StatusID in (1,2,6,9) --New, Suspended, Bad Address, Letter Series
	
	--Clear the service record 
	set @ServiceID = null
	
	--Does the service record exist for the debtor?
	select @ServiceID = ServiceID 
	from DebtorService DS
	inner join Debtor D on D.DebtorID = DS.DebtorID
	where InvoiceNo = @InvoiceNo
	and D.AccountNo = @PatientID 
	and D.OrgID = @OrgID
 
	--if the debtor is new and the service is new then insert
	if @DebtorID is null and @ServiceID is null
	begin
		
		if ltrim(rtrim(@RSPSSN)) = '' 
		begin
			if len(ltrim(rtrim(replace(@RSPSSN,'-','')))) = 9
			begin
				set @RSPSSN = ltrim(rtrim(replace(@PatientID,'-','')))
			end
			else
			begin
				set @RSPSSN = ''
			end
		end
		else
		begin
			set @RSPSSN = @RSPSSN
		end
 
		if ltrim(rtrim(@RSPLastName)) = '' 
		begin
			set @RSPLastName = @PatientLastName
			set @RSPFirstName = @PatientFirstName
		end
 
		if @RSPDOB is not null 
			set @RSPDOB = @RSPDOB
 
		--insert Debtor
		insert into Debtor(OrgID, SeriesID, TypeID, StatusID, LanguageID, PaidInFull, FirstName, LastName, AccountNo, SSN, 
				   BirthDate, CreateDate, CreateUserID, StatusDate, PhoneNo) 
		values(	@OrgID, @SeriesID, 2, 1, 1, 0, @RSPFirstName, @RSPLastName, @PatientID, 
			replace(@RSPSSN,'-',''), @RSPDOB, getdate(), 1, getdate(), @RSPPhone)
 
		--get the PK
		set @DebtorID = @@IDENTITY
 
		--log the events
		--Account Created
		set @EventDesc = @RSPFirstName + ' ' + @RSPLastName
		set @Today = getdate()
		exec P_INS_DEBTOREVENT @DebtorID, 6, 1, @Today, @SeriesID, null, @Today, 1, @EventDesc
 
		--Status Changed
		set @Today = getdate()
		exec P_INS_DEBTOREVENT @DebtorID, 10, 1, @Today, @SeriesID, null, @Today, 1
 
		--insert the debtor medical record
		insert into DebtorMedical(DebtorID, IsDebtor, PatientFirstName, PatientLastName, CreateDate, CreateUserID) 
			values (@DebtorID, case when @PatientLastName = @RSPLastname and @PatientFirstName = @RSPFirstName then 1 else 0 end, 	
				@PatientFirstName, @PatientLastName, getdate(), 1)
		
		--insert the debtor detail record
		insert into DebtorDetail(DebtorID) 
		values (@DebtorID)
		
		--insert the address record
		insert into Address(Address1, Address2, City, StateID, PostalCode, CountryID, CreateDate, CreateUserID) 
		values (@RSPAddress1, @RSPAddress2, @RSPCity, @RSPState, @RSPZip, 'US', getdate(), 1)
		
		--Get the PK
		set @AddressID = @@IDENTITY
		insert into DebtorAddress(DebtorID, AddressID, SequenceNo, CreateDate, CreateUserID, IsActive)
		values (@DebtorID, @AddressID, 1, getdate(), 1, 1)
		
		--log the event
		--Address Added
		set @EventDesc = @RSPAddress1 + ' ' + @RSPAddress2 + ' ' + @RSPCity + ', ' + @RSPState + '  ' + @RSPZip
		set @Today = getdate()
		exec P_INS_DEBTOREVENT @DebtorID, 4, 1, @Today, @SeriesID, null, @Today, 1, @EventDesc
		
		--Create the summary record
		insert into DebtorSummary(DebtorID, OriginalAmount, AmountDue, LastPayDate, DebtorSummaryTypeID, DebtorSummaryStatusID, CollAgencyID) 
		values (@DebtorID, 0, 0, '1/1/1900', 1, 1, 2)
		
		set @Count = @Count + 1
	end
 
 
	--Create the service record
	if @ServiceID is null
	begin
		set @NewServiceID = 0
		exec @NewServiceID = P_INS_DEBTORSERVICE @DebtorID, @DOS, @Amount, '', @InvoiceNo, @Amount, @DocDate
		if @NewServiceID <> 0 
			exec p_ins_DebtorInvoice 	
				@ServiceID = @NewServiceID,
				@OriginalInvoiceDate = 	@DocDate,
				@LastPayment = 0,
				@LastAdjustment = 0,
				@ReprintRequestUserID = 1 /* System */
		
		set @Balance = @Balance + @Amount
	end
 
	FETCH NEXT FROM C1
	INTO 	@PatientID, @RSPLastName, @RSPFirstName, @RSPAddress1, @RSPAddress2, @RSPCity, @RSPState, @RSPZip, @InvoiceNo, @RSPPhone, @DocDate, 
     		@DOS, @Amount, @PatientLastName, @PatientFirstName, @RSPDOB, @RSPSSN
 
END
CLOSE C1
DEALLOCATE C1
 
SET NOCOUNT OFF
 
RETURN
GO
[+][-]07.31.2008 at 11:12AM PDT, ID: 22132105

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: SQL Query Syntax
Tags: Microsoft, SQL, 2000
Sign Up Now!
Solution Provided By: jimhorn
Participating Experts: 2
Solution Grade: A
 
 
[+][-]07.31.2008 at 11:16AM PDT, ID: 22132133

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.31.2008 at 11:17AM PDT, ID: 22132142

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.31.2008 at 11:19AM PDT, ID: 22132155

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.31.2008 at 12:35PM PDT, ID: 22132828

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.31.2008 at 12:49PM PDT, ID: 22132921

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 08:11AM PDT, ID: 22139064

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 08:29AM PDT, ID: 22139271

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628