CODEWITHSUNDEEP
asp.netsql-server
Peter Sun
Peter Sun

Reputation: 1835

Compare Date in SQL Server

I building an ASP.NET application that connects to SQL Server 2008 R2. I having a brain fart on this one. I am trying to get some records between a date that the user chooses.

The application builds a sql statement and sends it to the server and waits for a return of records. The SQL statement looks like this.

SELECT * From policy pp where 20160115 between pp.policy_begin_date and pp.policy_end_date

It returns all records.

Do I need to convert '20160115' to a datetime variable? If so how do I convert that?

Upvotes: 1

Views: 46

Answers (3)

swe
swe

Reputation: 1455

You SHOULD cast/convert your string to date, because it is depending on the language of the server if it works.

Best way is to use convert and give information about language:

convert(date[time], '20160115', 112)

Upvotes: 1

McNets
McNets

Reputation: 10827

SELECT * 
From policy pp 
where cast('20160115' as datetime) between pp.policy_begin_date and pp.policy_end_date

You can use CAST or CONVERT

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56765

Yes. Like this:

... WHERE CAST('2016-01-15' AS DATE) BETWEEN ...

Unless it's really a datetime.

Upvotes: 1

Related Questions