CODEWITHSUNDEEP
sqlsql-servert-sqldate
Suganth G
Suganth G

Reputation: 5156

Given Date parameter is considered as Date Time parameter

I'm writing a stored procedure in sql!

I have to get records in the particular date.

I am using this query:

Declare @FromDate datetime
set @FromDate = '06/02/2014'

select * from Table where Date = @FromDate

Actually, in the Database there are 10 records in that date, but it is showing only two records because the @FromDate is taking like this 06/02/2014 00:00:00.000

If I write the query like this it means it works correctly!

select * from Table
where Date between '2014-08-28 00:00:00.000' and '2014-08-28 23:59:59.999'

How to solve this? I need to get all the records in that particular date.

Please help me !

Upvotes: 1

Views: 43

Answers (2)

Mike
Mike

Reputation: 3831

If @FromDate is of data type datetime and Table.Date is also of data type datetime then:

Declare @FromDate datetime = '2014-06-02';

Select Table.Date
From Table
Where Table.Date >= @FromDate And Date < DateAdd(day, 1, Table.Date)

Above, we create an inclusive lower boundary (anything equal to or later than 2014-06-02) and an exclusive upper boundary (anything earlier than 2014-06-03), but with a variable defined just once. So, effectively the query checks 2014-06-02 <= FromDate < 2014-06-03.

Upvotes: 2

Joe Mike
Joe Mike

Reputation: 1170

If you convert DateTime to Nvarchar your issue would be solved.

Try this query:

Declare @Date datetime='2014-08-28 00:00:00.000'

select * from Table
where  CONVERT(nvarchar(20),Date,105) = CONVERT(nvarchar(20),@Date,105)

Upvotes: 0

Related Questions