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

Question

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

Smash125

Hall of Fame

Points: 3562
More actions
May 15, 2019 at 12:02 pm

#3642297

select top 10 BLLG_END_DT,
SUBSTRING(BLLG_END_DT, 1, 10),
CAST(CKRFT.BLLG_END_DT AS VARCHAR(100))AS BLLG_END_DT
,CKRFT.TRKR_ST_DT,CAST(CKRFT.TRKR_ST_DT AS VARCHAR(100))AS Trkr_St_Dt
from DF_DENORM.FORECAST.CK_RFT_DEAL_PRD_CUST_ASSOC AS CKRFT

Existing records
2011-07-02 00:00:00.000
Expected record
2011-07-02

WHEN I RUN THIS SCRIPT I GET BELOW ERROR FOR SUBSTRING CODE
Argument data type datetime is invalid for argument 1 of substring function.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11649 More actions · Answer 1

Looks like you are trying to use a string function on a datetime field.

--instead of this
--SELECT SUBSTRING(GETDATE(),1,10)

--do this
SELECT CAST(GETDATE() AS DATE)

SELECT quote FROM brain WHERE original = 1
0 rows returned

Andrey SSC Eights! Points: 840 More actions · Answer 2

substring fuction expects a string to work with

you column BLLG_END_DT is of datetime type

to get the desired result try the following:

SELECT TOP 10 
BLLG_END_DT
--,SUBSTRING(BLLG_END_DT, 1, 10)
,format(BLLG_END_DT, 'yyyy-MM-dd') AS BLLG_END_DT
,CAST(CKRFT.BLLG_END_DT AS VARCHAR(100)) AS BLLG_END_DT
,CKRFT.TRKR_ST_DT
,CAST(CKRFT.TRKR_ST_DT AS VARCHAR(100)) AS Trkr_St_Dt
FROM DF_DENORM.FORECAST.CK_RFT_DEAL_PRD_CUST_ASSOC AS CKRFT

DesNorton SSC-Insane Points: 24386 More actions · Answer 3

Andrey wrote:

substring fuction expects a string to work with you column BLLG_END_DT is of datetime type to get the desired result try the following:
SELECT TOP 10 
BLLG_END_DT
--,SUBSTRING(BLLG_END_DT, 1, 10)
,format(BLLG_END_DT, 'yyyy-MM-dd') AS BLLG_END_DT
,CAST(CKRFT.BLLG_END_DT AS VARCHAR(100)) AS BLLG_END_DT
,CKRFT.TRKR_ST_DT
,CAST(CKRFT.TRKR_ST_DT AS VARCHAR(100)) AS Trkr_St_Dt
FROM DF_DENORM.FORECAST.CK_RFT_DEAL_PRD_CUST_ASSOC AS CKRFT

Beware of using FORMAT as it is extremely slow.

You will get much better performance by using CONVERT

SELECT TOP 10 
BLLG_END_DT
--,SUBSTRING(BLLG_END_DT, 1, 10)
,CONVERT(varchar(10), BLLG_END_DT, 120) AS BLLG_END_DT
,CAST(CKRFT.BLLG_END_DT AS VARCHAR(100)) AS BLLG_END_DT
,CKRFT.TRKR_ST_DT
,CAST(CKRFT.TRKR_ST_DT AS VARCHAR(100)) AS Trkr_St_Dt
FROM DF_DENORM.FORECAST.CK_RFT_DEAL_PRD_CUST_ASSOC AS CKRFT

How to post data/code on a forum to get the best help.