CODEWITHSUNDEEP
sql-server-2008
Big Pimpin
Big Pimpin

Reputation: 437

Displaying SQL Data For Same Day

I need to be able to display information where the search parameters show same day. I thought this query would cut the mustard, however it presents an error of 

Msg 241, Level 16, State 1, Line 13

Conversion failed when converting date and/or time from character string.

And this is my syntax, what should I do to remove the error?

DECLARE
  @startDate datetime,
  @endDate datetime

SET @startDate = '06/11/2015'
SET @endDate = '06/11/2015'

Set @startDate = convert(varchar(100),@startDate, 101) + ' 00:00:00 AM' 
Set @endDate = convert(varchar(100),@endDate, 101) + ' 23:59:59 PM'

select 'Artay' As [Bank Name]
,COUNT(Transaction) As LC
from dbo.financialtransactions
where cleardate between ''' + @startDate + ''' and ''' + @endDate + '''

Upvotes: 0

Views: 34

Answers (1)

Rahul
Rahul

Reputation: 77866

Change the last line to be

where cleardate between @startDate and @endDate

Your entire script should look like below, again Transaction is a reserve word and so make sure to escape it using []

DECLARE
  @startDate datetime,
  @endDate datetime

SET @startDate = '06/11/2015'
SET @endDate = '06/11/2015'

Set @startDate = convert(varchar(100),@startDate, 101) + ' 00:00:00 AM' 
Set @endDate = convert(varchar(100),@endDate, 101) + ' 23:59:59 PM'

select 'Artay' As [Bank Name]
,COUNT([Transaction]) As LC
from dbo.financialtransactions
where cleardate between @startDate and @endDate

Upvotes: 1

Related Questions