CODEWITHSUNDEEP
sql-serverstored-procedures
user2674855
user2674855

Reputation: 225

Conversion failed when converting date in stored procedure

I have store procedure like this:

ALTER procedure [dbo].[ParkingSummary1] @startdate varchar(100), @enddate varchar(100) as  begin
declare @date1 datetime = CONVERT(datetime, @startdate + ' 00:01:00.000', 120);
   declare @date2 datetime = CONVERT(datetime, @enddate + ' 23:23:59.000', 120);
DECLARE @cols AS NVARCHAR(MAX),
 @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Vtype) 
                    from VType_tbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT LocName, ' + @cols + ' 
            from 
            ( select l.LocName,Vtype from Transaction_tbl t  join VType_tbl v on t.vtid = v.vtid
  join dbo.Location_tbl l on t.locid=l.Locid    where dtime between '''+ @date1+''' and '''+@date2+'''  and Status = 5  ) d
            pivot 
            (
                count(Vtype)
                for Vtype in (' + @cols + ')
            ) p '  exec sys.sp_executesql @query
end

.while am passing startand enddate like this: 

@startdate = '2013-08-05',@enddate = '2013-08-08'

am getting error like this:Conversion failed when converting date and/or time from character string. what is wrong with my stored procedure ? instead of Date1 and date2 if i pass startdate and enddate then it will work.but that time if i given same date then not coming any result

Upvotes: 0

Views: 1413

Answers (2)

user2674855
user2674855

Reputation: 225

I had to replace

declare @date1 datetime = CONVERT(datetime, @startdate + ' 00:01:00.000', 120);
declare @date2 datetime = CONVERT(datetime, @enddate + ' 23:23:59.000', 120);

by

declare @date1 nvarchar(100) = convert(varchar, @startdate+' 00:00:00.000', 120)
declare @date2 nvarchar(100) = convert(varchar, @enddate+' 23:59:59.000', 120)

Upvotes: 0

James Culshaw
James Culshaw

Reputation: 1057

I would use SET DATEFORMAT YMD as the first line of your stored procedure.

ALTER procedure [dbo].[ParkingSummary1] @startdate varchar(100), @enddate varchar(100)
AS
BEGIN

SET DATEFORMAT DMY

declare @date1 datetime = CONVERT(datetime, @startdate + ' 00:01:00.000', 120);
declare @date2 datetime = CONVERT(datetime, @enddate + ' 23:23:59.000', 120);

DECLARE @cols AS NVARCHAR(MAX),
 @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Vtype) 
                    from VType_tbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT LocName, ' + @cols + ' 
            from 
            ( select l.LocName,Vtype from Transaction_tbl t  join VType_tbl v on t.vtid = v.vtid
  join dbo.Location_tbl l on t.locid=l.Locid    where dtime between '''+ @date1+''' and '''+@date2+'''  and Status = 5  ) d
            pivot 
            (
                count(Vtype)
                for Vtype in (' + @cols + ')
            ) p '  exec sys.sp_executesql @query
END

Upvotes: 1

Related Questions