CODEWITHSUNDEEP
sql-server-2008
Deepesh
Deepesh

Reputation: 5604

SQL Server convert function query

I have used a convert function in one of my Queries

Select * from tblMas where FromDate >= CONVERT(varchar(10), @FromDate,112)

@FromDate is a Datetime parameter, I don't know why but this query works fine.

Though it should not as FromDate is of DateTime field and we are comparing it with Varchar field.

CONVERT(varchar(10), @FromDate, 112) returns result in yyyyMMdd format. I am lost how SQL Server is comparing this and returning right result.

Upvotes: 1

Views: 693

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

There will be an implicit type conversion from varchar to datetime before the comparison.

If you have a look at the queryplan you will see something like this.

<ScalarOperator ScalarString="[FromDate]&gt;=CONVERT_IMPLICIT(datetime,CONVERT(varchar(10),[@FromDate],112),0)">

Another way to remove the time part from a datetime.

select * 
from tblMas 
where FromDate >= dateadd(day, datediff(day, 0, @FromDate), 0)

In SQL Server 2008 you can use

select * 
from tblMas 
where FromDate >= cast(@FromDate as date)

Upvotes: 3

Related Questions