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

  • Question

  • 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.

    Wednesday, May 15, 2019 12:56 PM

All replies

  • Hi,

    You have to convert to varchar :

    SUBSTRING(CAST(BLLG_END_DT AS VARCHAR(30)), 1, 10),

    Or convert it to date :

    CAST(BLLG_END_DT AS DATE)


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]



    Wednesday, May 15, 2019 1:10 PM
  • If the data type of the column BLLG_END_DT is datetime, you may not need to use the SUBSTRING. Instead, you just cast it to get the first 10 characters: 

    CAST(BLLG_END_DT AS VARCHAR(10))


    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 15, 2019 1:42 PM
  • Also, this works for you.

    declare @BLLG_END_DT datetime='2011-07-02 00:00:00.000'
    
    select replace(convert(varchar,@BLLG_END_DT,111),'/','-')

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 16, 2019 8:34 AM
    Moderator