Conversion failed when converting character string to smalldatetime data type.

Question

Conversion failed when converting character string to smalldatetime data type.

shani19831

SSCommitted

Points: 1737
More actions
September 11, 2012 at 2:47 am

#267590

Good Day all
I have been working on a union query for a while, this afternoon i had to add a field to calculate the number of days an account is in arrears with.
When i ran the query as a union it does not retrieve any data instead throws out an error
"Conversion failed when converting character string to smalldatetime data type."
but if i ran each part on its own then it brings back the data.
Please help me out to sort this error or am i doing something wrong?
Below you will find my query in its original form.
Select top 10 "Company Reg No" =
CASE
WHEN a.id_value IS NULL THEN 'Not Company'
ELSE a.id_value
END,
a.sic_code as 'Industry', a.tin as 'Tax ID', a.tin_cert_dt as 'Registration Date', a.status as 'Company Status', a.closing_reason_id as 'Closure Reason',
b.name_1 as 'Company Name', b.name_2 as 'Trading Name', b.city as 'Town', b.country_code as 'Country', b.email_addr_2 as 'Email', b.province, b.district, b.address_line_2 as 'Location', b.address_line_3 as 'Physical Address', b.address_line_1 as 'Physical Address1', b.phone_1,
c.od_option as 'Overdraft Type', c.acct_no, c.acct_type, c.status as 'Acct Status' ,c.create_dt, d.closed_dt as 'Closed DT', d.dormant_dt as 'Dormant DT',d.update_dt as 'Update DT',c.last_sys_maint_dt as 'Change DT',c.period as 'Repayment', c.trm as 'Terms_Duration', c.mat_dt as 'Maturity_Date', c.no_signatures as 'JointParticipants',
d.cur_bal, Disbursed_Amount=NULL, d.update_dt AS 'Change Date', Amount_paid= d.cur_bal-d.od_limit , d.od_limit as 'Principal', Delinquency_Date=NULL, Installment_Arrears=NULL,
first_pmt_dt=NULL, Scheduled_Payment=null,
base_rate=null, current_rate=null,
g.iso_code as 'Currency Type',
Approved_Amount =null, Interest_Type=null,Last_Payment_Date=null,d.closed_dt as 'Date Closed',
Approval_Date=null, no_days_arrear=isnull(i.no_days_delq,0) ,BOZ = case when i.no_days_delq < 90 then 'Pass'
when i.no_days_delq between 91 and 119 then 'Substandard'
when i.no_days_delq between 120 and 179 then 'Doubtfull'
when i.no_days_delq > 180 then 'Loss'
END
from
dp_acct_CBL c
LEFT OUTER JOIN
dp_display_CBL d on c.rim_no = d.rim_no and c.acct_no = d.acct_no and c.acct_type = d.acct_type
LEFT OUTER JOIN
rm_acct_CBL a on c.rim_no = a.rim_no
LEFT OUTER JOIN
rm_address_CBL b ONa.rim_no = b.rim_no
LEFT OUTER JOIN
dbo.ad_gb_crncy_CBL g on d.crncy_id = g.crncy_id
left outer join (select acct_no, acct_type,
no_days_delq=datediff(day, overlmt_dt, (select last_from_dt from dbo.ov_control_CBL))
from dbo.dp_display_CBL
where od_option = 'A') as i on
c.acct_no = i.acct_no and
c.acct_type = i.acct_type
where a.rim_type = 'NonPersonal' --and c.rim_no = '18707'
UNION
SELECT top 10 "Company Reg No" =
CASE
WHEN a.id_value IS NULL THEN 'Not Company'
ELSE a.id_value
END,
a.sic_code as 'Industry', a.tin as 'Tax ID', a.tin_cert_dt as 'Registration Date', a.status as 'Company Status', a.closing_reason_id as 'Closure Reason',
b.name_1 as 'Company Name', b.name_2 as 'Trading Name', b.city as 'Town', b.country_code as 'Country', b.email_addr_2 as 'Email', b.province, b.district, b.address_line_2 as 'Location', b.address_line_3 as 'Physical Address', b.address_line_1 as 'Physical Address1', b.phone_1,
od_option = NULL, f.acct_no, f.acct_type,f.status as 'Acct Status' ,f.create_dt, e.closed_dt as 'Closed DT',dormant_dt='LOAN',e.update_dt as 'Update DT',last_sys_maint_dt='LOAN' ,f.period, f.trm, f.mat_dt, JointParticipants=null,
e.CUR_BAL, e.AMT_FINANCED as 'Disbursed Amount', e.UPDATE_DT, e.accr_dr, e.PAYOFF, e.DELQ_DT as 'Delinquency Date', e.NO_TIMES_DELQ as 'Installment in Arrears',
c.first_pmt_dt, c.amt as 'Scheduled_Payment',
d.base_rate, d.current_rate,
g.iso_code as 'Currency Type',
e.AMT_FINANCED as 'Approved Amount',e.int_type as 'Interest Type', e.LAST_PMT_DT as 'Last Payment Date', e.CLOSED_DT,
f.contract_dt, no_days_arrear=isnull(i.no_days_delq,0) ,BOZ = case when i.no_days_delq < 90 then 'Pass'
when i.no_days_delq between 91 and 119 then 'Substandard'
when i.no_days_delq between 120 and 179 then 'Doubtfull'
when i.no_days_delq > 180 then 'Loss'
DoubtfulEND
from
ln_acct_CBL f
LEFT OUTER JOIN
ln_display_CBL e on f.rim_no = e.RIM_NO and f.acct_no = e.acct_no and f.acct_type = e.acct_type
LEFT OUTER JOIN
dbo.ln_pmt_schedule_CBL c on f.acct_no = c.acct_no and f.acct_type = c.acct_type
LEFT OUTER JOIN
rm_acct_CBL a on f.rim_no = a.rim_no
LEFT OUTER JOIN
rm_address_CBL b ONf.rim_no = b.rim_no
LEFT OUTER JOIN
ln_acct_int_opt_CBL d on f.acct_no = d.acct_no and f.acct_type = d.acct_type
LEFT OUTER JOIN
dbo.ad_gb_crncy_CBL g on e.crncy_id = g.crncy_id
left outer join (select acct_no, acct_type, delq_amt,
no_days_delq=datediff(day, delq_dt, (select last_from_dt from dbo.ov_control_CBL))
from dbo.ln_display_CBL
where delq_flag = 1) as i on
f.acct_no = i.acct_no and
f.acct_type = i.acct_type
where a.rim_type = 'NonPersonal' --and c.status = 'Active'
--and f.rim_no = '18707'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

One of the column you return in one of your "selects" (I think it's a first one

) is of smalldatetime data type (we don't know which column exactly, as you didn't post any ddl and code looks like noodles a bit). The position of this column in this "select" corresponds to position of "character" data type column from second "select", where character data cannot be converter implicitly to datetime.

First thing I would advise you to do is to format your code, so you could read it easily. It can help you to find this column just by comparing to select lists. You can format it to something like:

Select top 10 =

CASE

WHEN a.id_value IS NULL THEN 'Not Company'

ELSE a.id_value

END AS [Company Reg No]

,a.sic_code AS [Industry]

,a.tin AS [Tax ID]

,a.tin_cert_dt AS [Registration Date]

,a.status AS [Company Status]

, ...

etc.

You will see that code formatted as above is easier to maintain and debug.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

Check the data types of date columns in your SELECT list

I assume you have some column with CHAR or VARCHAR as their datatype, convert them to SMALLDATETIME or DATETIME

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

shani19831 SSCommitted Points: 1737 More actions · Answer 3

Eugene Elutin (9/11/2012)
One of the column you return in one of your "selects" (I think it's a first one
) is of smalldatetime data type (we don't know which column exactly, as you didn't post any ddl and code looks like noodles a bit). The position of this column in this "select" corresponds to position of "character" data type column from second "select", where character data cannot be converter implicitly to datetime.
First thing I would advise you to do is to format your code, so you could read it easily. It can help you to find this column just by comparing to select lists. You can format it to something like:
Select top 10 =
CASE
WHEN a.id_value IS NULL THEN 'Not Company'
ELSE a.id_value
END AS [Company Reg No]
,a.sic_code AS [Industry]
,a.tin AS [Tax ID]
,a.tin_cert_dt AS [Registration Date]
,a.status AS [Company Status]
, ...
etc.

You will see that code formatted as above is easier to maintain and debug.

Eugene, thanks for this hint, the code does look handy in this format. Let me format it and see if i can find anything wrong and i will post back.

Regards,

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

shani19831 (9/11/2012)
Eugene Elutin (9/11/2012)
One of the column you return in one of your "selects" (I think it's a first one
) is of smalldatetime data type (we don't know which column exactly, as you didn't post any ddl and code looks like noodles a bit). The position of this column in this "select" corresponds to position of "character" data type column from second "select", where character data cannot be converter implicitly to datetime.
First thing I would advise you to do is to format your code, so you could read it easily. It can help you to find this column just by comparing to select lists. You can format it to something like:
Select top 10 =
CASE
WHEN a.id_value IS NULL THEN 'Not Company'
ELSE a.id_value
END AS [Company Reg No]
,a.sic_code AS [Industry]
,a.tin AS [Tax ID]
,a.tin_cert_dt AS [Registration Date]
,a.status AS [Company Status]
, ...
etc.

You will see that code formatted as above is easier to maintain and debug.
Eugene, thanks for this hint, the code does look handy in this format. Let me format it and see if i can find anything wrong and i will post back.
Regards,

There's a syntax error in the second query of the union: the last word in the FROM list.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden