CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Umair Arif
Umair Arif

Reputation: 117

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)

Upvotes: 0

Views: 92

Answers (2)

PowerStar
PowerStar

Reputation: 895

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)

Upvotes: 1

Munavvar
Munavvar

Reputation: 821

Also Conver your column into Datetime. and try again

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)

Upvotes: 0

Related Questions