September 4, 2013 at 4:27 am
Hi Guys
I got a table that I have created I'm trying to insert data to that table:And I'm using a Case function to insert data on my Finmonth column
CREATE TABLE [dbo].[SalesLog_TerminationsTest](
[Accountno] [int] NULL,
[Contractno] [int] NULL,
[Terminatedate] [datetime] NULL,
[MonthTerminate] [varchar](20) NULL,
[YearTerminate] [varchar](5) NULL,
[Registrationnumber] [varchar](150) NULL,
[Editopr] [varchar](150) NULL,
[Username] [varchar](150) NULL,
[Terminatecode] [varchar](150) NULL,
[Terminatedesc] [varchar](150) NULL,
[unitid] [varchar](150) NULL,
[unittypeid] [varchar](150) NULL,
[UnitType] [varchar](150) NULL,
[description] [varchar](350) NULL,
[salesID] [varchar](150) NULL,
[salesIDDesc] [varchar](150) NULL,
[contracttype] [varchar](150) NULL,
[ContractDesc] [varchar](150) NULL,
[Remarks] [varchar](350) NULL,
[VehicleMake] [varchar](250) NULL,
[VehicleModel] [varchar](250) NULL,
[LenghtOfContract] [int] NULL,
[FitmentID] [varchar](50) NULL,
[FitmentIDDesc] [varchar](250) NULL,
[ChargeCode] [varchar](50) NULL,
[ChargeAmount] [money] NULL,
[ClientName] [varchar](250) NULL,
[ContractDate] [datetime] NULL,
[MonthContract] [varchar](20) NULL,
[YearContract] [varchar](5) NULL,
[MonthTerminated] [varchar](100) NULL,
[FinYear] [int] NULL,
[FinMonth] [int] NULL,
[FinQuarter] [int] NULL
) ON [PRIMARY]
Quey for inserting data for my finMonth column
CASE
WHEN finmonth BETWEEN '2013-02-25' AND '2013-03-26' THEN 1
WHEN finmonth between '2013-03-26' and '2013-04-26' THEN 2
WHEN finmonth BETWEEN '2013-04-26' AND '2013-05-29' THEN 3
WHEN finmonth BETWEEN '2013-05-29'and '2013-06-26' THEN 4
WHEN finmonth BETWEEN '2013-06-26' AND '2013-07-29' THEN 5
WHEN finmonth BETWEEN '2013-07-29' AND '2013-08-28' THEN 6
WHEN finmonth BETWEEN '2013-08-28' AND '2013-09-26' THEN 7
WHEN finmonth BETWEEN '2013-09-26' AND '2013-10-29' THEN 8
WHEN finmonth BETWEEN '2013-10-29' AND '2013-11-27' THEN 9
WHEN finmonth BETWEEN '2013-11-27' AND '2013-12-23' THEN 10
END AS FinMonth,
I get the following error Msg
Msg 245, Level 16, State 1, Line 244
Conversion failed when converting the varchar value '2013-02-25' to data type int.
Please assist
September 4, 2013 at 4:49 am
The error is due to the implicit conversion of the datatypes within the CASE BETWEEN statement.
The INT datatype (from field [finmonth]) must be compared to the string value. Therefor the string value wil be implicitly converted to the INT datatype. You should use the DATEPART or the DATEDIFF function to evaluate the [finmonth] value.
September 4, 2013 at 4:57 am
Please can we see your whole INSERT statement? I don't understand why, if you're inserting into FinMonth, you're testing for the value that's already there. Also, FinMonth is integer, so it can never be BETWEEN '2013-02-25' AND '2013-03-26'.
John
September 4, 2013 at 5:11 am
DECLARE @startdate DATETIME,
@enddate VARCHAR(50),
@accountno INTEGER,
@contractno INTEGER,
@contracttype INTEGER,
@maxtermdate DATETIME
SET @accountno = 0
SET @contractno = 0
--set @maxtermdate = (Select MAX(terminatedate) from MixControl.dbo.SalesLog_Terminations)
SET @startdate = '2011-04-01'
SET @contracttype = 2
DECLARE @startofday DATETIME,
@endofday DATETIME
--select @startofday = dbo.rsf_startofday(convert(datetime,@startdate ))
--select @endofday = dbo.rsf_endofday(convert(datetime,@enddate ))
--Select @startofday, @endofday
IF @accountno = 0
BEGIN
SELECT @accountno = NULL
END
IF @contractno = 0
BEGIN
SELECT @contractno = NULL
END
IF @contracttype = 2
BEGIN
SELECT @contracttype = NULL
END
SELECT DISTINCT ctl.contractno,
ctl.accountno,
ctl.terminatedate,
ctl.editopr,
'username' = Isnull(rts.username, rts.usercode),
Isnull(ctl.terminatecode, '') AS 'terminatecode',
Isnull(tmr.terminatedesc, '') AS 'terminatedesc',
'registrationnumber' = Space(15),
'unitid' = Space(20),
'unittypeid' = 0,
'UnitType' = Space(150),
'description' = Space(50),
'startdate' = CONVERT(DATETIME, @startdate),
'enddate ' = CONVERT(DATETIME, @enddate),
'salescode' = Space(20),
--hgc 15/11/2011 SamID 126177
'salesdesc' = Space(255),
'contracttype' = Space(6),
'vehiclemake' = Space(255),
'vehiclemodel' = Space(255),
'clientname' = Space(255),
'contractdate' = Space(40),
'chargecode' = Space(20),
'chargeamount' = Space(20),
'fitmentID' = Space(20),
'fitmentIDDesc' = Space(255)
INTO #term
FROM contractlog ctl WITH (nolock)
LEFT OUTER JOIN terminatereason tmr WITH (nolock)
ON ctl.terminatecode = tmr.terminatecode
LEFT OUTER JOIN rts5user rts WITH (nolock)
ON ctl.editopr = rts.usercode
WHERE ctl.terminatedate > @startdate
AND ctl.accountno = Isnull(@accountno, ctl.accountno)
AND ctl.contractno = Isnull(@contractno, ctl.contractno)
UPDATE #term
SET registrationnumber = ***.registrationnumber,
unitid = uni.box_no,--deu.unitid,
unittypeid = Isnull(unt.unittypeid, ''),
UnitType = unt.UnitTypeDesc,
description = unt.unittypedesc,--unt.description,
salescode = sal.code,
salesdesc = sal.description,
vehiclemake = amm.assetmakedesc,
vehiclemodel = am.assetmodeldesc,
clientname = cl.accountname,
contractdate = con.createdate,
fitmentid = con.fitmentcentreid,
fitmentiddesc = b.brokername
FROM #term ctl WITH (nolock)
LEFT JOIN mixcontrol.dbo.contractasset cna WITH(nolock)
ON cna.contractid = ctl.contractno
AND cna.isactive = 0
LEFT JOIN mixcontrol.dbo.contract con WITH(nolock)
ON con.contractid = cna.contractid
AND con.isactive = 0
LEFT JOIN mixcontrol.dbo.client cl WITH(nolock)
ON con.clientid = cl.clientid
LEFT JOIN mixcontrol.dbo.asset *** WITH(nolock)
ON ***.assetid = cna.assetid
LEFT JOIN mixcontrol.dbo.assetmodel am WITH(nolock)
ON ***.assetmodelid = am.assetmodelid
LEFT JOIN mixcontrol.dbo.assetmake amm WITH(nolock)
ON am.assetmakeid = amm.assetmakeid
LEFT JOIN mixcontrol.dbo.unit uni WITH(nolock)
ON uni.assetid = cna.assetid
--and uni.IsActive = 0
LEFT JOIN mixcontrol.dbo.unittype unt WITH(nolock)
ON unt.unittypeid = uni.unittypeid
LEFT JOIN new_config.dbo.salespersons sal WITH(nolock)
ON sal.code = con.salespersonid
LEFT JOIN rslead.dbo.broker b WITH(nolock)
ON con.fitmentcentreid = Cast(b.brokerno AS VARCHAR(15))
-- All Units except CA
UPDATE #term
SET contracttype = CASE
WHEN ( chr.escalchargeflag = 1 ) THEN 'RENTAL'
ELSE 'CASH'
END,--ds 17/06/2007
chargecode = cch.chargecode,
chargeamount = cch.chargeamt
FROM #term ctl WITH (nolock)
LEFT OUTER JOIN contractcharge cch WITH (nolock)
ON cch.contractno = ctl.contractno
LEFT OUTER JOIN charge chr WITH (nolock)
ON cch.chargecode = chr.chargecode
WHERE chr.escalchargeflag = Isnull(@contracttype, chr.escalchargeflag)
AND LEFT(unitid, 2) <> 'CA'
-- CA Units
UPDATE #term
SET contracttype = CASE
WHEN ( cch.bouquetno = 5 ) THEN 'RENTAL'
WHEN ( cch.bouquetno = 4 ) THEN 'CASH'
ELSE 'OTHER'
END,--ds 17/06/2007
chargecode = cch.servicecode,
chargeamount = cch.serviceamt
FROM #term ctl WITH (nolock)
LEFT OUTER JOIN contractservice cch WITH (nolock)
ON cch.contractno = ctl.contractno
WHERE LEFT(unitid, 2) = 'CA'
AND cch.servicecode = 'BASE'
SELECT contractno,
accountno,
terminatedate,
Datename(mm, terminatedate) AS MonthTerminate,
Datename(yyyy, terminatedate) AS YearTerminate,
editopr,
username,
terminatecode,
Isnull(terminatedesc, '') AS terminatedesc,
registrationnumber,
unitid,
unittypeid,
description,
salescode,
salesdesc,
contracttype,
sl.contractdesc,
UnitType,
vehiclemake,
vehiclemodel,
clientname,
contractdate,
Datename(mm, contractdate) AS MonthContract,
Datename(yyyy, contractdate) AS YearContract,
chargecode,
chargeamount,
fitmentid,
fitmentiddesc,
'remarks' = Space(255),
Datename(mm, terminatedate) + ' '
+ Datename(yyyy, terminatedate) AS MonthTerminated,
Datediff(mm, contractdate, terminatedate) AS LenghtOfContract,
Datepart(yyyy, terminatedate) AS FinYear,
Datepart(mm, terminatedate) AS FinMonth
INTO #tempterminations --drop table #tempTerminations
FROM #term t
LEFT JOIN mixcontrol.dbo.saleslogterminationdesc sl
ON t.terminatecode = sl.termcode
-- Get all Reloads
SELECT *,
Replace(username, ' ', '.') AS TermUser
INTO #temreload --drop table #temReload
FROM #tempterminations
WHERE contractdesc = 'Sameday Term and Reload'
SELECT tt.contractno,
tt.registrationnumber,
tt.unitid,
st.contractid AS [NewContractID]
INTO #tempreloadregnum --drop table #tempReloadRegNum
FROM #temreload tt
LEFT JOIN mixcontrol.dbo.saleslog_contract st
ON Rtrim(Ltrim(Replace(tt.registrationnumber, ' ', ''))) = Rtrim(
Ltrim(
Replace(st.registrationno, ' ', '')))
WHERE tt.terminatedate < st.contractdate
--and st.SalesLogID is not null --33
SELECT tt.contractno,
tt.registrationnumber,
tt.unitid,
st.contractid AS [NewContractID]
INTO #tempreloadaccount
FROM #temreload tt
LEFT JOIN mixcontrol.dbo.saleslog_contract st
ON tt.accountno = st.clientid
WHERE tt.contractno NOT IN (SELECT contractno
FROM #tempreloadregnum)
--and st.SalesLogID is not null
AND tt.terminatedate < st.contractdate
AND tt.description = st.unittype
AND Parsename(tt.termuser, 2) = st.oprcontractpush
UPDATE #tempterminations
SET remarks = 'Query'
--select * from #tempTerminations
WHERE contractno NOT IN (SELECT contractno
FROM #tempreloadregnum)
AND contractno NOT IN (SELECT contractno
FROM #tempreloadaccount)
AND contractdesc = 'Sameday Term and Reload'
UPDATE p
SET p.remarks = 'New ContractID '
+ Cast(pc.[newcontractid] AS VARCHAR(15))
--Select *
FROM #tempterminations p
JOIN #tempreloadregnum pc
ON ( p.contractno = pc.contractno )
UPDATE p
SET p.remarks = 'New ContractID '
+ Cast(pc.[newcontractid] AS VARCHAR(15))
--Select *
FROM #tempterminations p
JOIN #tempreloadaccount pc
ON ( p.contractno = pc.contractno )
TRUNCATE TABLE mixcontrol.dbo.saleslog_terminationsTest
INSERT INTO mixcontrol.dbo.saleslog_terminationsTest
(accountno,
contractno,
terminatedate,
monthterminate,
yearterminate,
registrationnumber,
editopr,
username,
terminatecode,
terminatedesc,
unitid,
unittypeid,
UnitType,
[description],
salesid,
salesiddesc,
contracttype,
contractdesc,
remarks,
vehiclemake,
vehiclemodel,
lenghtofcontract,
fitmentid,
fitmentiddesc,
chargecode,
chargeamount,
clientname,
contractdate,
monthcontract,
yearcontract,
monthterminated,
finyear,
finmonth,
finquarter)
SELECT accountno,
contractno,
terminatedate,
monthterminate,
yearterminate,
registrationnumber,
editopr,
username,
terminatecode,
terminatedesc,
unitid,
unittypeid,
UnitType,
[description],
salescode,
salesdesc,
contracttype,
contractdesc,
remarks,
vehiclemake,
vehiclemodel,
lenghtofcontract,
fitmentid,
fitmentiddesc,
chargecode,
chargeamount,
clientname,
contractdate,
monthcontract,
yearcontract,
monthterminated,
--FinYear,
CASE
WHEN finmonth IN ( 4, 5, 6, 7,
8, 9, 10, 11, 12 ) THEN finyear
ELSE finyear - 1
END AS FinYear,
CASE
WHEN finmonth BETWEEN '2013-02-25' AND '2013-03-26' THEN 1
WHEN finmonth between'2013-03-26' and '2013-04-26' THEN 2
WHEN finmonth BETWEEN '2013-04-26' AND '2013-05-29' THEN 3
WHEN finmonth BETWEEN '2013-05-29'and '2013-06-26' THEN 4
WHEN finmonth BETWEEN '2013-06-26' AND '2013-07-29' THEN 5
WHEN finmonth between '2013-07-29' AND '2013-08-28' THEN 6
WHEN finmonth between '2013-08-28'and '2013-09-26' THEN 7
WHEN finmonth BETWEEN '2013-09-26' AND '2013-10-29' THEN 8
WHEN finmonth between '2013-10-29' AND '2013-11-27' THEN 9
WHEN finmonth between '2013-11-27' AND '2013-12-23' THEN 10
END AS FinMonth,
CASE
WHEN finmonth IN ( 4, 5, 6 ) THEN 1
WHEN finmonth IN ( 7, 8, 9 ) THEN 2
WHEN finmonth IN ( 10, 11, 12 ) THEN 3
WHEN finmonth IN ( 1, 2, 3 ) THEN 4
END AS FinQuarter
FROM #tempterminations
ORDER BY terminatedate ASC
September 4, 2013 at 5:22 am
This is how you define the FinMonth column in #tempterminations, and therefore it's always going to be an integer between 1 and 12:
Datepart(mm, terminatedate) AS FinMonth
All you need to do is lose the CASE expression in your final INSERT statement and just insert FinMonth instead.
By the way, if you have any control over the design of that table, I advise you to change it. You should store the date once, and not split it into separate columns for month and year.
John
September 4, 2013 at 7:50 am
I need my finmonth column to be generic that's why I'm not using it.I use CASE because I'm given dates to use for Finmonth.
September 4, 2013 at 9:24 am
No, you're not. FinMonth is an integer. Here's the proof from your original post:
[FinMonth] [int]
And here's what you're trying to do with it:
WHEN finmonth BETWEEN '2013-02-25' AND '2013-03-26' THEN 1
WHEN finmonth between'2013-03-26' and '2013-04-26' THEN 2
WHEN finmonth BETWEEN '2013-04-26' AND '2013-05-29' THEN 3
WHEN finmonth BETWEEN '2013-05-29'and '2013-06-26' THEN 4
WHEN finmonth BETWEEN '2013-06-26' AND '2013-07-29' THEN 5
WHEN finmonth between '2013-07-29' AND '2013-08-28' THEN 6
WHEN finmonth between '2013-08-28'and '2013-09-26' THEN 7
WHEN finmonth BETWEEN '2013-09-26' AND '2013-10-29' THEN 8
WHEN finmonth between '2013-10-29' AND '2013-11-27' THEN 9
WHEN finmonth between '2013-11-27' AND '2013-12-23' THEN 10
An integer can't be between two dates.
John
September 4, 2013 at 9:41 am
John Mitchell-245523 (9/4/2013)
An integer can't be between two dates.John
Yes, it can be between 2 dates (sort of). The problem here is with data type precedence.
SQL Server can't compare between different data types and will do an implicit conversion. When comparing integers and strings it will always try to convert to integer and that's the reason for this error. If you change the strings for strings with valid integers ('20130225') or to real dates (CAST('2013-02-25' AS datetime)) it won't return an error but might not return the correct results.
My suggestion is to use a calendar table with the months defined as needed for this case.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply