Implicit conversion data type datetime to int NO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Implicit conversion data type datetime to int NO

Author

Topic

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 13:06:14

Hi,
I am trying to create a table, then insert data into that table. I keep getting this error and am not sure what to do to fix it as I am fairly new to SQL. (I am rebuilding access databases in SQL. here is my code, any help would be greatly appreciated.

Drop Table Josh.dbo."Reference: PO History"
;
CREATE TABLE Josh.dbo."Reference: PO History"
(PohdNbr int,PohdRef nvarchar(15),PodtLine smallint,PodtStat nvarchar(1),PodtType nvarchar(1),ApveVendId
nvarchar(6),
AptbBuyrCode nvarchar(6),InitItemNbr nvarchar(30),ApveName nvarchar(30),PohdToName nvarchar(30),PohdToAdr1
nvarchar(30),
PohdToAdr2 nvarchar(30),PohdToAdr3 nvarchar(30),PodtDesc1 nvarchar(35),"Forecast week" datetime,PodtShipDate
datetime,
PodtExptDate datetime,PodtAckDate datetime,PodtQtyOrd float,Received float,"Open" float,PodtCostTot float,PodtCost
float,
PohdCtrlNbr int,PohdOrdrDate datetime)
;
Insert Into "Reference: PO History" (PohdNbr,Pohdref,PodtLine,PodtStat,PodtType,ApveVendId,AptbBuyrCode,InitItemNbr,ApveName,PohdToName,
PohdToAdr1,PohdToAdr2,PohdToAdr3,PodtDesc1,"Forecast week",PodtShipDate,PodtExptDate,PodtAckDate,PodtQtyOrd,Received,"Open",
PodtCostTot,PodtCost,PohdCtrlNbr,PohdOrdrDate)
SELECT
root_PO_HEAD.PohdNbr,
root_PO_HEAD.PohdRef,
root_PO_DETAIL.PodtLine,
root_PO_DETAIL.InitItemNbr,
root_PO_DETAIL.PodtStat,
root_PO_DETAIL.PodtShipDate,
root_PO_DETAIL.PodtExptDate,
root_PO_DETAIL.PodtAckDate,
root_PO_DETAIL.PodtQtyOrd,
Sum((
CASE
WHEN [PordQtyRec] IS NULL THEN 0
ELSE [PordQtyRec]
END)) AS Received,
(CASE
WHEN [PodtStat] = 'C' THEN 0
ELSE [PodtQtyOrd] - (Sum((
CASE
WHEN [PordQtyRec] IS NULL THEN 0
ELSE [PordQtyRec]
END)))
END) AS "Open",
root_AP_VEND_MAST.ApveName,
root_PO_HEAD.ApveVendId,
root_PO_DETAIL.PodtType,
root_PO_HEAD.AptbBuyrCode,
root_PO_DETAIL.PodtCostTot,
root_PO_DETAIL.PodtCost,
(CASE
WHEN datepart(weekday, [PodtExptDate]) = 2 THEN dateadd(day, -1, [PodtExptDate])
ELSE (CASE
WHEN datepart(weekday, [PodtExptDate]) = 3 THEN dateadd(day, -2, [PodtExptDate])
ELSE (CASE
WHEN datepart(weekday, [PodtExptDate]) = 4 THEN dateadd(day, -3, [PodtExptDate])
ELSE (CASE
WHEN datepart(weekday, [PodtExptDate]) = 5 THEN dateadd(day, -4, [PodtExptDate])
ELSE (CASE
WHEN datepart(weekday, [PodtExptDate]) = 6 THEN dateadd(day, -5, [PodtExptDate])
ELSE (CASE
WHEN datepart(weekday, [PodtExptDate]) = 7 THEN dateadd(day, -6, [PodtExptDate])
ELSE [PodtExptDate]
END)
END)
END)
END)
END)
END) AS "Forecast Week",
root_PO_HEAD.PohdCtrlNbr,
root_PO_HEAD.PohdToName,
root_PO_HEAD.PohdToAdr1,
root_PO_HEAD.PohdToAdr2,
root_PO_HEAD.PohdToAdr3,
root_PO_HEAD.PohdOrdrDate,
root_PO_DETAIL.PodtDesc1
FROM
((root_PO_HEAD
LEFT JOIN root_PO_DETAIL
ON (root_PO_HEAD.PohdNbr = root_PO_DETAIL.PohdNbr) AND (root_PO_HEAD.PohdCtrlNbr = root_PO_DETAIL.PohdCtrlNbr))
LEFT JOIN root_PO_RECEIPT_DET
ON
(root_PO_DETAIL.PohdNbr = root_PO_RECEIPT_DET.PordNbr) AND
(root_PO_DETAIL.PohdCtrlNbr = root_PO_RECEIPT_DET.PordCtrlNbr) AND
(root_PO_DETAIL.PodtLine = root_PO_RECEIPT_DET.PordLine) AND
(root_PO_DETAIL.InitItemNbr = root_PO_RECEIPT_DET.InitItemNbr))
LEFT JOIN root_AP_VEND_MAST
ON root_PO_HEAD.ApveVendId = root_AP_VEND_MAST.ApveVendId
GROUP BY
root_PO_HEAD.PohdNbr,
root_PO_HEAD.PohdRef,
root_PO_DETAIL.PodtLine,
root_PO_DETAIL.InitItemNbr,
root_PO_DETAIL.PodtStat,
root_PO_DETAIL.PodtShipDate,
root_PO_DETAIL.PodtExptDate,
root_PO_DETAIL.PodtAckDate,
root_PO_DETAIL.PodtQtyOrd,
root_AP_VEND_MAST.ApveName,
root_PO_HEAD.ApveVendId,
root_PO_DETAIL.PodtType,
root_PO_HEAD.AptbBuyrCode,
root_PO_DETAIL.PodtCostTot,
root_PO_DETAIL.PodtCost,
(
CASE
WHEN datepart(weekday, [PodtExptDate]) = 2 THEN dateadd(day, -1, [PodtExptDate])
ELSE (
CASE
WHEN datepart(weekday, [PodtExptDate]) = 3 THEN dateadd(day, -2, [PodtExptDate])
ELSE (
CASE
WHEN datepart(weekday, [PodtExptDate]) = 4 THEN dateadd(day, -3, [PodtExptDate])
ELSE (
CASE
WHEN datepart(weekday, [PodtExptDate]) = 5 THEN dateadd(day, -4, [PodtExptDate])
ELSE (
CASE
WHEN datepart(weekday, [PodtExptDate]) = 6 THEN dateadd(day, -5, [PodtExptDate])
ELSE (
CASE
WHEN datepart(weekday, [PodtExptDate]) = 7 THEN dateadd(day, -6, [PodtExptDate])
ELSE [PodtExptDate]
END)
END)
END)
END)
END)
END),
root_PO_HEAD.PohdCtrlNbr,
root_PO_HEAD.PohdToName,
root_PO_HEAD.PohdToAdr1,
root_PO_HEAD.PohdToAdr2,
root_PO_HEAD.PohdToAdr3,
root_PO_HEAD.PohdOrdrDate,
root_PO_DETAIL.PodtDesc1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 13:09:33

this means you're trying to insert datetime values to an integer field. look in your select list to spot cases where source column is of type datetime whereas corresponding column on insert list is of type integer.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 13:11:10

My guess is..your last insert field is a datetime field...but you are trying to insert the value root_PO_DETAIL.PodtDesc1 which kinda looks like a description...

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 13:18:26

There are only two int fields in the table that data in being inserted into, as you can see in the create statement. Both those fields are also Int fields in the source table, so what is the issue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 13:19:15

I think problem is you've columns jumbled in select list (for example 4th column in list is root_PO_DETAIL.InitItemNbr whereas corresponding field on target table is PodtStat which is coming after root_PO_DETAIL.InitItemNbr in select. you need to give them in same order in select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 13:19:59

Thanks I will try that post the result.

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 15:22:59

worked great. thanks for the info, like I said, I am new to SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:13:09

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources