SQL 参数数据类型日期时间对于子字符串函数的参数 1 无效

SQL Argument data type datetime is invalid for argument 1 of substring function

提问by Robert Woods

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:

我正在尝试运行 sql 查询以将数据从一个表移动到另一个表中，但我遇到了问题。这是我的查询：

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) <> 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 

The errors I'm getting are:

我得到的错误是：

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.

Now I've tried

现在我试过了

CAST(S_ReportingDate as nvchar(8))

but that didn't fix the problem, does anyone have any ideas?

但这并没有解决问题，有人有任何想法吗？

回答by Mike Clark

SUBSTRING() function accepts character data types like char, varchar, nchar, nvarchar, text etc.

SUBSTRING() 函数接受字符数据类型，如 char、varchar、nchar、nvarchar、text 等。

If we pass, a DATETIME datatype, it doesn't know what to do.

如果我们传递一个 DATETIME 数据类型，它不知道该怎么做。

the values of it are formatted like 2015-01-13 00:00:00.000, but they are not. This is just the standard format of the connection that makes the datetime value readable. You can use the CONVERT function with a suitable format number to convert your datetime to a string. If you only need the date part of the datetime, then my preferred format is 112, which format it as YYYYMMDD.

它的值的格式类似于 2015-01-13 00:00:00.000，但它们不是。这只是使日期时间值可读的连接的标准格式。您可以使用具有合适格式编号的 CONVERT 函数将日期时间转换为字符串。如果您只需要日期时间的日期部分，那么我的首选格式是 112，格式为 YYYYMMDD。

In other words, just pass "CONVERT(char(8), date, 112)" to your substring function.

换句话说，只需将“CONVERT(char(8), date, 112)”传递给您的子字符串函数。

If you also need the time part, then check Books Online for a more appropriate format.

如果您还需要时间部分，请查看联机丛书以获得更合适的格式。

回答by TTeeple

It looks like you are trying to extract the year, month, and date out of a datetime field using string operations. You can do a simple convert on datetime fields to the appropriate format:

看起来您正在尝试使用字符串操作从日期时间字段中提取年、月和日期。您可以将日期时间字段简单转换为适当的格式：

CONVERT (varchar(15), GETDATE(), 112)

If you need it as an INT, then just wrap that in another CONVERT(int)function.

如果您需要它作为INT，那么只需将其包装在另一个CONVERT(int)函数中。