Implicit conversion error

Question

Post reply

Implicit conversion error

EasyBoy

SSCertifiable

Points: 5301
More actions
November 19, 2010 at 3:48 am

#243068

While i am creating clustered index on view, i am getting following error message.That most proably due to dateconnversion.
Here is the view
CREATE VIEW StatEntityFinlPeriod WITH SCHEMABINDING AS
SELECT
KeyFinlStatPeriod = x2_0.KeyFinlStatPeriod,
KeyFinlStat = x1_0.KeyFinlStat,
KeyFinlStatEOP = x1_0.KeyFinlStatEOP,
Annualization = x1_0.Annualization,
KeyStatEntity = x0_0.KeyStatEntity,
DateEndedStandard = x0_0.DateEndedStandard,
KeyInsuranceStatLOB = x0_0.KeyInsuranceStatLOB,
InsuranceStatReportedDate = x0_0.InsuranceStatReportedDate,
DataSourcePeriod = x2_0.DataSourcePeriod,
FiscalQuarter = x2_0.FiscalQuarter,
AnnualizationStandard = x1_0.AnnualizationStandard,
MostRecentSequence = x0_0.MostRecentSequence,
MostRecentSequenceFiscalYears = x0_0.MostRecentSequenceFiscalYears,
DateBegun = CONVERT(smalldatetime,
CASE WHEN x2_0.FiscalQuarter IN ('1','2','3','4')
THEN DATEADD (mm,-3,DATEADD(dd,1,x0_0.DateEndedStandard))
WHEN x2_0.FiscalQuarter IN ('Y','L')
THEN DATEADD (yy,-1,DATEADD(dd,1,x0_0.DateEndedStandard))
WHEN x2_0.FiscalQuarter IN ('T')
THEN (CONVERT(varchar(4),YEAR(x0_0.DateEndedStandard)))
END),
AppStatus = CONVERT(smallint,2),
UpdOperation = CONVERT(tinyint,0),
UpdDate = CONVERT(datetime,'1/1/1900',101)
FROM dbo.FinlStatEOP x0_0
INNER JOIN dbo.FinlStat x1_0 on x0_0.KeyFinlStatEOP = x1_0.KeyFinlStatEOP AND x1_0.UpdOperation < 2
INNER JOIN dbo.FinlStatPeriod x2_0 on x1_0.KeyFinlStat = x2_0.KeyFinlStat and x2_0.UpdOperation < 2
WHERE x0_0.UpdOperation < 2
and x0_0.FinlStatPeriodOffset = 0

Viewing 3 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

What is the error?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WayneS SSC Guru Points: 95461 More actions · Answer 2

What are the data types of all of the fields in this part of your query?

INNER JOIN dbo.FinlStat x1_0 on x0_0.KeyFinlStatEOP = x1_0.KeyFinlStatEOP AND x1_0.UpdOperation < 2

INNER JOIN dbo.FinlStatPeriod x2_0 on x1_0.KeyFinlStat = x2_0.KeyFinlStat and x2_0.UpdOperation < 2

WHERE x0_0.UpdOperation < 2

and x0_0.FinlStatPeriodOffset = 0

Also, can you post the CREATE TABLE statements for these tables?

dbo.FinlStatEOP

dbo.FinlStat

dbo.FinlStatPeriod

Thanks for helping us help you!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2