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 · Accepted 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 <> '' and @DateLast <> ''
   SET @FinalSQL  = @FinalSQL + '  OR CONVERT(Date, DateLog) >= ''' + 
                    CONVERT(VARCHAR(50), @DateFirst, 126) +   -- convert @DateFirst to string for concatenation!
                    ''' AND CONVERT(Date, DateLog) <=''' +  -- 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.

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

[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