Open Forum

Expand all | Collapse all

Microsoft Sql Server MS Query Invalid date Format Error

  • 1.  Microsoft Sql Server MS Query Invalid date Format Error

    Posted Mar 12, 2019 03:15 PM
    I keep getting this error in my query for my parameter dates.


    I want to date to be mm/dd/yyyy, but in my table, the date is mm/dd/yyyy 00:00:00 am/pm.  Is there a way to correct this in my query view?

    ------------------------------
    Lanisha McLendon
    BurgerBusters Inc.
    Virginia Beach VA
    ------------------------------


  • 2.  RE: Microsoft Sql Server MS Query Invalid date Format Error

    GOLD CONTRIBUTOR
    Posted Mar 12, 2019 04:19 PM
    CAST(DATE_FIELD AS DATE) AS Column_Name

    ------------------------------
    Chase Landorf
    Director of I.T.
    Ideal Deals, LLC dba Ideal Aluminum Products
    Saint Augustine FL
    ------------------------------



  • 3.  RE: Microsoft Sql Server MS Query Invalid date Format Error

    TOP CONTRIBUTOR
    Posted Mar 13, 2019 08:26 AM
    I have also had problems passing a date parameter from Excel as a parameter of a stored procedure. If the parameter is a DATETIME, Excel assumes that you've also entered the time with the date value, otherwise it complains.

    A simple fix that I use is to declare the parameter as a VARCHAR and convert it to a date in the stored procedure like this:

    CREATE PROCEDURE _YourStoredProcedure
      @StartDateString VARCHAR(50)
    AS
    DECLARE @StartDate DATETIME = CONVERT(DATETIME, @StartDateString

    There is no error checking here, so if a not date is passed to this stored procedure, you will get an error  :)

    Good Luck & let me know if you have any questions or problems!​​

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------



  • 4.  RE: Microsoft Sql Server MS Query Invalid date Format Error

    GOLD CONTRIBUTOR
    Posted Mar 13, 2019 08:29 AM
    In your SQL proc always convert date time from external entities or anything to the needed format Dynamics needs.

    CONVERT(date, getdate())

    or

    CONVERT(date, @<MyPassedVariable>)



    ------------------------------
    Kerry Hataley
    CEO & President
    Nanook Software, Inc
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role