Umair Arif Umair Arif - 10 days ago 5
SQL Question

Conversion of Date in SQL Server

I am trying to make a comparison on the basis of CreatedDate but it gives me an error like


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


here is my query as @startdate and @endDate are my parameters for the Stored procedure.

declare @startDate as varchar(100) = null
declare @endDate as varchar(100) = null
set @startDate = '01/11/2016'
set @endDate = '30/11/2016'

SELECT CreatedDate FROM Table1
WHERE HCCreatedDate BETWEEN
CONVERT(Datetime, ISNULL(@startDate,''), 103)
AND CONVERT(Datetime, ISNULL(@endDate,''), 103)

Answer

Please convert the HCCreatedDate column as below

declare @startDate as varchar(100) = null
declare @endDate as varchar(100) = null
set @startDate = '01/11/2016'
set @endDate = '30/11/2016'

SELECT CreatedDate FROM Table1 
WHERE CONVERT(Datetime,HCCreatedDate,103) BETWEEN 
CONVERT(Datetime, ISNULL(@startDate,''), 103)
    AND CONVERT(Datetime, ISNULL(@endDate,''), 103)