Need help urgently 1

[Vusco]

I'm trying to insert data into a table using a select statement as my source,within this select statement I'm retrieving a field that contains a date that is stored as an integer value within,but SQL doesn't like it.

Statement:

EXEC
('INSERT INTO dbo.STG_AML_Customers_list
SELECT
CO_CDE AS CompanyCode,
CUST_FULL_NAME_1 AS CustomerName,
CUST_REC_OPEN_DATE AS CustomerOpenDate,
CUST_TYPE AS CustomerType,
CUST_NO CustomerNumber,
KYC_IND AS KYCIndicator


FROM andwhs01.cpc.dbo.DIM_CUSTOMER_MONTHLY_'+ @Reporting_Month + '
WHERE co_cde = 15
')

Below is the error message it provides:

Server: Msg 260, Level 16, State 1, Procedure SP_aml_Customer_List, Line 26
Disallowed implicit conversion from data type datetime to data type int, table 'CPC.dbo.AML_Customers_list', column 'CustomerOpenDate'. Use the CONVERT function to run this query.

 

[danvlas]

Check the order of the fields in the table.
Or, better, define the order in the Insert statement.
I suspect you're trying to insert a Date value into an Int field.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ESquared]

Why are you using dynamic SQL?

specify your destination columns INSERT Table (Column1, Column2) SELECT Expression1, Expression2 FROM Table2

 

[SQLDenis]

>>I'm retrieving a field that contains a date that is stored as an integer value within

this was answered the other day, do a search for Unix date and have fun figuring out the timezone difference

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

You can find it here: thread183-1463525

Select DateAdd(Second, YourColumnName, '19700101') As YourDateColumn

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

Good catch Denis I didn't even read carefully. Star.

 

[Vusco]

Can someone provide me with the know how of getting Index selectivity

 

[SQLDenis]

>>Can someone provide me with the know how of getting Index selectivity

something like this

number of unique keys/number of rows
nonclustered indexex with a selectivity less than 0.1 are not efficient and the optimizer will probably not use them

But you have to test this for yourself

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

BTW,

Need descriptive subject urgently.