Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conversion failed when converting character string to smalldatetime data type. Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 2:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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 ON a.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'
Doubtful END
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 ON f.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'

Post #1357200
Posted Tuesday, September 11, 2012 3:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1357232
Posted Tuesday, September 11, 2012 3:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:34 AM
Points: 2,666, Visits: 4,736
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/
Post #1357234
Posted Tuesday, September 11, 2012 3:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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,
Post #1357237
Posted Tuesday, September 11, 2012 3:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1357246
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse