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 13 days ago
    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 13 days ago
    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 12 days ago
    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 12 days ago
    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
    ------------------------------