CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2005
HEEN
HEEN

Reputation: 4721

Executing the stored procedure causes error

I have a stored procedure in which I want to get the reportdate while executing.

I pass one parameter to the stored procedure for executing it,. I pass it like this

exec UserReportData '10-06-2016'

but I get an error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is my stored procedure:

ALTER PROCEDURE [dbo].[UserReportData] 
    @As_ONDATE Datetime 
AS 
BEGIN 
    DECLARE @REPORTDATE datetime        
    --DECLARE @OPENING INT      

    SELECT * 
    INTO #temptable
    FROM
        (SELECT 
             a.CUser_id, b.User_Id, a.U_datetime AS REPORTDATE
         FROM 
             inward_doc_tracking_trl a, user_mst b
         WHERE
             a.CUser_id = b.mkey
             AND CONVERT(varchar(50), a.U_datetime, 103) = @As_ONDATE) AS x

    DECLARE Cur_1 CURSOR FOR 
        SELECT CUser_id, User_Id 
        FROM #temptable

    OPEN Cur_1

    DECLARE @CUser_id INT
    DECLARE @User_Id INT

    FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SELECT CONVERT(varchar(50), U_datetime, 103) 
        FROM inward_doc_tracking_trl                        
        WHERE CONVERT(varchar(50), U_datetime, 103) = @As_ONDATE

        UPDATE #temptable
        SET REPORTDATE = @REPORTDATE
        WHERE CUser_id = @CUser_id
          AND User_Id = @User_Id

        FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id
    END

    CLOSE Cur_1
    DEALLOCATE Cur_1

    SELECT * FROM #temptable

    DROP TABLE #temptable                           
END

Kindly help me know what is the cause of the error.

Upvotes: 2

Views: 269

Answers (2)

Ajay2707
Ajay2707

Reputation: 5798

You are passing datetime, by default SQL Server uses MMDDYYYY or YYYYMMDD format.

You are using the DD-MM-YYYY format,

Either you write as

set dateformat dmy --set ddmmyyyy format
exec UserReportData '10-06-2016'
set dateformat mdy  --set default again

or you passed the value which is in proper format. No other solution work.

Upvotes: 0

marc_s
marc_s

Reputation: 754348

The various settings (language, date format) only influence how the DateTime is shown to you in SQL Server Management Studio - or how it is parsed when you attempt to convert a string to a DateTime.

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

  • YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

  • YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

The recommendation for SQL Server 2008 and newer is to use DATE if you only need the date portion, and DATETIME2(n) when you need both date and time. You should try to start phasing out the DATETIME datatype if ever possible

So in your concrete case - just change how you call your stored procedure to:

exec UserReportData '20160610'    -- 10th of June, 2016

or

exec UserReportData '20161006'    -- 6th of October, 2016

depending on whether this was the 6th October or the 10th June of 2016 you're interested in ...

Upvotes: 2

Related Questions