CODEWITHSUNDEEP
sql-serversql-server-2008
Vince Osana
Vince Osana

Reputation: 417

SQL Server : varchar convert to datetime error

I am getting an error

Conversion failed when converting date and/or time from character string.

But I still get this result

enter image description here

And I also want to ask if why is the time still showing when I use the 102 date format.

ALTER PROCEDURE [dbo].[mPDF_gen048] 



@gfromDate varchar(10), -- '3/16/2016'
@gtoDate varchar(10) -- '3/18/2016'


AS
BEGIN
    SET NOCOUNT ON;

declare @fromDate date
declare @toDate date
declare @totaldays int

set @fromDate  = (select convert (varchar(10), @gfromDate, 101))
set @toDate  = (select convert (varchar(10), @gtoDate, 101))

select @fromDate

set @totaldays = (select datediff(day,@fromdate,@toDate)+2)

declare @sql varchar(max) = ''
declare @tablename varchar(20)
declare @counter int = 1


while @counter < @totaldays
begin
    set @tablename = (select upper(convert(varchar(8), @fromDate, 106)))
    set @tablename = replace(@tablename,' ','')


    set @sql = @sql + 'SELECT uploadtime, updatedby, updatetime, FROM gen_048_' +  @tablename + ' WHERE refdate = ' + @fromDate

    if(@counter < @totaldays-1)

    set @fromDate = dateadd(day,1,@fromDate)
    set @counter = @counter + 1



end

--print @sql
exec (@sql)

end

Upvotes: 0

Views: 208

Answers (1)

StackUser
StackUser

Reputation: 5398

Try with this code,

ALTER PROCEDURE [dbo].[mPDF_gen048] @gfromDate VARCHAR(10) = '3/16/2016'
    ,@gtoDate VARCHAR(10) = '3/18/2016'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @fromDate DATE
    DECLARE @toDate DATE
    DECLARE @totaldays INT

    SET @fromDate = (
            SELECT convert(VARCHAR(10), @gfromDate, 101)
            )
    SET @toDate = (
            SELECT convert(VARCHAR(10), @gtoDate, 101)
            )

    SELECT @fromDate

    SET @totaldays = (
            SELECT datediff(day, @fromdate, @toDate) + 2
            )

    DECLARE @sql VARCHAR(max) = ''
    DECLARE @tablename VARCHAR(20)
    DECLARE @counter INT = 1

    WHILE @counter < @totaldays
    BEGIN
        SET @tablename = (
                SELECT upper(convert(VARCHAR(8), @fromDate, 106))
                )
        SET @tablename = replace(@tablename, ' ', '')
        SET @sql = @sql + 'SELECT uploadtime, updatedby, updatetime FROM gen_048_' + @tablename + ' WHERE refdate = ''' + convert(VARCHAR(10), @fromDate) + ''''

        IF (@counter < @totaldays - 1)
            SET @fromDate = dateadd(day, 1, @fromDate)
        SET @counter = @counter + 1
    END

    --PRINT @sql
    EXEC (@sql)
END

Upvotes: 1

Related Questions