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.

• Y.B.

  SSChampion

  Points: 11652

  More actions

  May 15, 2019 at 12:45 pm

  #3642317

  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

  May 15, 2019 at 12:47 pm

  #3642319

  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: 24511

  More actions

  May 15, 2019 at 2:33 pm

  #3642397

  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.