Type datetime for input parameter in procedure

Question

I have created a procedure with this structure but it doesn t work for datetime input parameter   I executed this query but   declare  a datetime declare  b datetime   set  a  2012 04 06 12 23 45  set  b  2012 08 06 21 10 12   exec LogProcedure  AccountLog   N test    a   b   but SQL Server got me this error     Conversion failed when converting date and or time from character string    but when I test with this query it works    exec LogProcedure  AccountLog  N test    Stored procedure code   alter PROCEDURE LogProcedure      TableName VARCHAR 60        SearchString NVARCHAR 50        DateFirst DateTime            DateLast DateTime      AS BEGIN     SET NOCOUNT ON      DECLARE  FinalSQL      NVARCHAR MAX       SET  FINALSQL    SELECT   FROM       TableName      where 1 2         IF  DateFirst  lt  gt     and  DateLast  lt  gt            set  FinalSQL     FinalSQL      or convert  Date DateLog   gt            DateFirst     and convert  Date DateLog   lt       DateLast        SELECT          FinalSQL     FinalSQL     or       SYSCOLUMNS NAME      LIKE N        SearchString               FROM SYSCOLUMNS      WHERE OBJECT NAME id     TableName     AND TYPE NAME SYSCOLUMNS XTYPE  IN   VARCHAR   NVARCHAR   CHAR   NCHAR   INT   DECIMAL       ORDER BY COLID      EXEC  FinalSQL  END    This query is true too  SELECT    FROM AccountLog  where 1 2  or convert  Date  DateLog   gt    2012 04 06   and convert  Date  DateLog   lt   2012 08 06

User · Answer

In this part of your SP   IF  DateFirst  lt  gt     and  DateLast  lt  gt        set  FinalSQL     FinalSQL             or convert  Date DateLog   gt              DateFirst            and convert  Date DateLog   lt         DateLast     you are trying to concatenate strings and datetimes   As the datetime type has higher priority than varchar nvarchar  the   operator  when it happens between a string and a datetime  is interpreted as addition  not as concatenation  and the engine then tries to convert your string parts     or convert  Date DateLog   gt           and others  to datetime or numeric values  And fails   That doesn t happen if you omit the last two parameters when invoking the procedure  because the condition evaluates to false and the offending statement isn t executed   To amend the situation  you need to add explicit casting of your datetime variables to strings   set  FinalSQL     FinalSQL          or convert  Date DateLog               convert date   DateFirst          and convert  Date DateLog   lt        convert date   DateLast    You ll also need to add closing single quotes   set  FinalSQL     FinalSQL          or convert  Date DateLog               convert date   DateFirst                 and convert  Date DateLog   lt        convert date   DateLast

User · Answer

You should use the ISO-8601 format for string representations of dates - anything else is dependent on the SQL Server language and dateformat settings    The ISO-8601 format for a DATETIME when using only the date is    YYYYMMDD  no dashes or antyhing    For a DATETIME with the time portion  it s YYYY-MM-DDTHH MM SS  with dashes  and a T in the middle to separate date and time portions    If you want to convert a string to a DATE for SQL Server 2008 or newer  you can use YYYY-MM-DD  with the dashes  to achieve the same result  And don t ask me why this is so inconsistent and confusing - it just is  and you ll have to work with that for now   So in your case  you should try   declare  a datetime declare  b datetime   set  a    2012-04-06T12 23 45    -- 6th of April  2012 set  b    2012-08-06T21 10 12    -- 6th of August  2012  exec LogProcedure  AccountLog   N test    a   b   Furthermore - your stored proc has problem  since you re concatenating together datetime and string into a string  but you re not converting the datetime to string first  and also  you re forgetting the close quotes in your statement after both dates   So change this line here to this   IF  DateFirst  lt  gt     and  DateLast  lt  gt        SET  FinalSQL     FinalSQL      OR CONVERT Date  DateLog   gt                              CONVERT VARCHAR 50    DateFirst  126      -- convert  DateFirst to string for concatenation                          AND CONVERT Date  DateLog   lt         -- you need closing quotes after  DateFirst                      CONVERT VARCHAR 50    DateLast  126              -- convert  DateLast to string and also  closing tags after that missing    With these settings  and once you ve fixed your stored procedure which contains problems right now  it will work

[sql-server-2008] Type datetime for input parameter in procedure

Examples related to sql-server-2008

Examples related to datetime

Examples related to stored-procedures

Examples related to input-parameters