title: SQL Argument data type datetime is invalid for argument 1 of substring function
body: <p>I'm trying to run an sql query to move data from one table into another but I'm having trouble. Here is my query:</p>
  
  <pre><code>TRUNCATE TABLE LIQ_ACCT_EOD_SIF
  
  DECLARE @filedt varchar(32), @repdate int;
  SET NOCOUNT ON
  
  
  SET @filedt = CAST(getdate() as varchar);
  
  select @repdate=CAST(right(S_ReportingDate, 4) + SUBSTRING(S_ReportingDate,4,2) + LEFT(S_ReportingDate, 2)
  AS int)
  FROM AllocationJunLLv2;
  
  DELETE FROM dbo.LIQ_ACCT_EOD_SIF
  WHERE REPORT_DATE=@repdate;
  
  
  INSERT INTO dbo.LIQ_ACCT_EOD_SIF
  (
   FILE_NUM
  ,FILE_DATE_TIME
  ,ROW_NUM
  ,FILE_TYPE
  ,MIGRATION_DATE
  ,INSTITUTION_NAME
  ,RESULT_TYPE_NAME
  ,SCENARIO_NAME
  ,REPORT_DATE
  ,ASSET_CLASS_NAME
  ,INSTRUMENT_TYPE_NAME
  ,INSTRUMENT_SUBTYPE_NAME
  ,INSTRUMENT_NAME
  ,COUNTERPARTY_NAME
  ,CORPORATE_RETAIL_FLG
  ,CLIENT_NAME
  ,DEPOSIT_FLG
  ,POSITION_FLG
  ,LIQUID_FLG
  ,LOCATION_NAME
  ,PORTFOLIO_NAME
  ,ACCOUNT_TYPE_NAME
  ,ACCOUNT_NUMBER
  ,ACCOUNT_OPENING_DATE
  ,INSTRUMENT_ISO_CURRENCY
  ,ISO_CURRENCY_CODE
  ,CURRENT_VALUE
  ,MATURITY_DATE
  ,MATURITY_VALUE
  ,DRAWN_AMOUNT
  ,UNDRAWN_AMOUNT
  ,LIMIT_AMOUNT
  ,EXPOSURE_CD
  ,EXPECTED_CASH_FLOW
  ,EXPECTED_CASH_FLOW_DATE
  ,PASSED_DUE_IND
  ,INT_TYPE
  ,INT_FIXED_RATE
  ,INT_VAR_BASENAME
  ,INT_REL_VAR_RATE
  ,RATING_NAME
  ,RATING_AGENCY
  ,BASEL_OPTION
  ,RATING_TYPE
  ,REPORT_LINE_NUMBER
  ,REPORT_LINE_NAME
  ,DELTA
  ,GAMMA
  ,NETTING_NUMBER
  ,COLLATERAL_ACCOUNT_NUMBER
  ,BUFFER_ELIGIBLE
  ,ALTERNATIVE_YIELD_RATE
  ,FTP_PORTFOLIO
  ,REPO_FLAG
  ,LIMIT_EXPIRY_DATE
  ,AL_DEFINITION
  ,REPO_AMOUNT
  ,TRANSACTION_NUMBER
  ,UNENCUMBERED_AMOUNT
  ,MARGIN_ACCOUNT_NUMBER
  ,CASH_FLOW_TYPE
  ,value_of_house
  ,security_value
  ,security_type
  ,int_rate_floor
  ,int_rate_ceiling
  ,lcr_line_number
  ,AMM_line_number
  ,BASEL_APPROACH
  ,CCR_portfolio
  ,CCR_sector
  ,CCR_region
  )
  
  SELECT
   1 AS FILE_NUM
  ,@filedt AS FILE_DATE_TIME
  ,0 AS ROW_NUM
  ,'EOD' AS FILE_TYPE
  ,getdate() AS MIGRATION_DATE    
  ,'UBNL' AS INSTITUTION_NAME 
  ,LEFT(b.STBITEM, 32) AS RESULT_TYPE_NAME
  ,'Unknown' AS SCENARIO_NAME
  ,@repdate AS REPORT_DATE
  ,ISNULL(rl.ASSET_CLASS_NAME, 'Unknown') AS ASSET_CLASS_NAME
  ,'Unknown' AS INSTRUMENT_TYPE_NAME
  ,'Unknown' AS INSTRUMENT_SUBTYPE_NAME
  ,'Unknown' AS INSTRUMENT_NAME
  ,LEFT(b.S_InstitutionCode, 32) AS COUNTERPARTY_NAME
  ,ISNULL(rl.CORPORATE_RETAIL_FLG, 'U')
  ,b.S_CustomerName AS CLIENT_NAME
  ,'U' AS DEPOSIT_FLG
  ,'U' AS POSITION_FLG
  ,'U' LIQUID_FLG
  ,'Unknown' AS LOCATION_NAME
  ,b.S_Ref1 AS PORTFOLIO_NAME
  ,'Unknown' AS ACCOUNT_TYPE_NAME
  ,b.Unique_ID AS ACCOUNT_NUMBER
  ,CAST( right(CAST(S_StartDate AS varchar(8)),4) + SUBSTRING(CAST(S_StartDate AS varchar(8)),4,2) + LEFT(CAST(S_StartDate AS varchar(8)), 2)AS int) 
  AS ACCOUNT_OPENING_DATE
  
  ,'USD' AS INSTRUMENT_ISO_CURRENCY
  ,'USD' AS ISO_CURRENCY_CODE
  ,CASE
      WHEN rl.CURRENT_VALUE_FLG='Y' THEN 
      CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
      ELSE 0
   END AS CURRENT_VALUE
  ,ISNULL(CAST( right(S_MaturityDate,4) + SUBSTRING(S_MaturityDate,4,2) + LEFT(S_MaturityDate, 2)AS int), 29991231) AS MATURITY_DATE
  ,CASE
      WHEN rl.MATURITY_VALUE_FLG='Y' THEN
      CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
      ELSE 0
   END AS MATURITY_VALUE
  ,0 AS DRAWN_AMOUNT
  ,CASE
      WHEN rl.UNDRAWN_AMOUNT_FLG='Y' THEN 
      CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
      ELSE 0
   END AS UNDRAWN_AMOUNT
  ,CASE
      WHEN rl.LIMIT_AMOUNT_FLG='Y' THEN 
      CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
      ELSE 0
   END AS LIMIT_AMOUNT
  ,ISNULL(rl.EXPOSURE_CD, 'Unknown') AS EXPOSURE_CD
  ,CASE
      WHEN rl.EXPECTED_CASH_FLOW_FLG='Y' THEN 
      CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
      ELSE 0
   END AS EXPECTED_CASH_FLOW
  ,CASE
      WHEN rl.EXPECTED_CASH_FLOW_FLG='Y' THEN ISNULL(CAST( right(S_MaturityDate,4) + SUBSTRING(S_MaturityDate,4,2) + LEFT(S_MaturityDate, 2)AS int), 29991231) 
      ELSE 0
   END AS EXPECTED_CASH_FLOW_DATE
  ,'U' AS PASSED_DUE_IND
  ,'U' AS INT_TYPE
  ,0 AS INT_FIXED_RATE
  ,'Unknown' AS INT_VAR_BASENAME
  ,0 AS INT_REL_VAR_RATE
  ,'Unknown' AS RATING_NAME
  ,'Unknown' AS RATING_AGENCY
  ,'B2L' AS BASEL_OPTION
  ,'LT' AS RATING_TYPE
  ,ISNULL(CAST(SUBSTRING(b.STBITEM, 5, 2) AS int), 0) AS REPORT_LINE_NUMBER
  ,ISNULL(rl.REPORT_LINE_NAME, 'Unknown') AS REPORT_LINE_NAME
  ,0 AS DELTA
  ,0 AS GAMMA
  ,'Unknown' AS NETTING_NUMBER
  ,'Unknown' AS COLLATERAL_ACCOUNT_NUMBER
  ,'U' AS BUFFER_ELIGIBLE
  ,0 AS ALTERNATIVE_YIELD_RATE
  ,'Unknown' AS FTP_PORTFOLIO
  ,'U' AS REPO_FLAG
  ,0 AS LIMIT_EXPIRY_DATE
  ,ISNULL(rl.AL_DEFINITION, 'U') AS AL_DEFINITION
  ,0 AS REPO_AMOUNT
  ,'Unknown'AS TRANSACTION_NUMBER
  ,0 AS UNENCUMBERED_AMOUNT
  ,'Unknown' AS MARGIN_ACCOUNT_NUMBER
  ,CASE
      WHEN rl.REPORT_LINE_NUMBER BETWEEN 6 AND 17 THEN 7
      WHEN rl.REPORT_LINE_NUMBER=23 THEN 6
      ELSE 1
   END AS CASH_FLOW_TYPE
  ,0 as value_of_house
  ,0 as security_value
  ,'Unknown' as security_type
  ,0 AS INT_RATE_FLOOR
  ,0 AS INT_RATE_CEILING
  ,'Unk' as LCR_LINE_NUMBER
  ,'Unk' as AMM_LINE_NUMBER
  ,'Unknown' as basel_approach
  ,'Unknown' as CCR_portfolio
  ,'Unknown' as CCR_sector
  ,'Unknown' as CCR_region
  FROM AllocationJunLLv2 b
  
  INNER JOIN LIQ_EXCH_RATE_SIF fx ON 'USD'=fx.SOURCE_CURRENCY_CD
  INNER JOIN DM_REPORT_LINE rl 
  ON CAST(SUBSTRING(b.STBITEM, 5, 2) AS int)=rl.REPORT_LINE_NUMBER AND rl.REPORT_NUMBER=48
   WHERE ISNULL(CAST(CAST(
    CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE   dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END
    AS float)
    AS numeric(22,6))
  , 0) &lt;&gt; 0
  AND NOT (
  LEFT(b.STBITEM, 3) = 'F48' AND 
  RIGHT(b.STBITEM, 2) = 'C1' AND 
  CAST(SUBSTRING(b.STBITEM, 5, 2) AS int) BETWEEN 6 AND 17
  )
  
  
  UPDATE LIQ_ACCT_EOD_SIF
  SET 
      CURRENT_VALUE=-CURRENT_VALUE,
      MATURITY_VALUE=-MATURITY_VALUE
  WHERE
      REPORT_DATE=@repdate
  
  GO 
  </code></pre>
  
  <p>The errors I'm getting are:</p>
  
  <pre><code>Msg 8116, Level 16, State 1, Line 9
  Argument data type datetime is invalid for argument 1 of substring function.
  Msg 8116, Level 16, State 1, Line 17
  Argument data type datetime is invalid for argument 1 of substring function.
  </code></pre>
  
  <p>Now I've tried </p>
  
  <pre><code>CAST(S_ReportingDate as nvchar(8))
  </code></pre>
  
  <p>but that didn't fix the problem, does anyone have any ideas?</p>