Philip Horan (12/18/2008)
I am attempting to INSERT from a temporary table. I created the temporary table from an excel file. The data types for the temporary table do not match the table I wish to INSERT INTO.The table I am selecting from has a field 'PurchaseDate' nvarchar(255). The date is held as follows:
2004-04-26 00:00:00
The field I want to INSERT INTO has a data type of datetime
My insert query is as follows:
INSERT INTO [MyDB].[dbo].[CrossReferences]
(
Product,
Supplier,
SupplierSequence,
NormalCost,
DateLastQuoted,
CreatedUser,
CreatedDate,
LastModifiedUser,
LastModifiedDate,
LeadTime,
Comments
)
SELECT
Products.Product,
Suppliers.Supplier,
998,
Price,
cast(substring(PurchaseDate,5,4) + substring(PurchaseDate,1,2) + substring(PurchaseDate,3,2) as datetime),
'admin',
getdate(),
'admin',
getdate(),
999,
' '
FROM dbo.temp
INNER JOIN dbo.Products ON temp.ProductId = Products.ProductID
INNER JOIN dbo.Suppliers ON temp.SupplierId = Suppliers.SupplierID
Error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
Any Ideas,
Many Thanks,
Phil.
Since you seem to indicate the date in PurchaseDate is already stored as yyyy-mm-dd hh:mi:ss, why are you trying to substring the data to convert it? Your code should be straight forward:
INSERT INTO [MyDB].[dbo].[CrossReferences]
(
Product,
Supplier,
SupplierSequence,
NormalCost,
DateLastQuoted,
CreatedUser,
CreatedDate,
LastModifiedUser,
LastModifiedDate,
LeadTime,
Comments
)
SELECT
Products.Product,
Suppliers.Supplier,
998,
Price,
cast(PurchaseDate as datetime),
'admin',
getdate(),
'admin',
getdate(),
999,
' '
FROM dbo.temp
INNER JOIN dbo.Products ON temp.ProductId = Products.ProductID
INNER JOIN dbo.Suppliers ON temp.SupplierId = Suppliers.SupplierID
If this isn't the case, then you need to provide us more information. I'd suggest that you take a little time to read the article that I have linked below in my signature block regarding asking for assistance.