user3431935 user3431935 - 7 months ago 15
SQL Question

converting date time failure with datatype varchar

My database table name = "SearchTask" looks like this
Data types of columns

Task = varchar
AssignedDate = varchar

**Task | AssignedDate**
A | 01/02/2016
A | 02/03/2016
A | 05/04/2016
A | '
A | '


PS: 3rd and 4th row are char(39), they are added when the user does not want any assigned date and this rule cannot be changed. It has to be like this by the customer.

Now when I write a query

select task, AssignedDate
from SearchTask
where AssignedDate not like char(39)
and convert(varchar(10), convert(date,assigneddate, 105),112) >= 20160301
and convert(varchar(10), convert(date,assigneddate, 105),112) <= 20160331


it gives me error message

conversion failed when converting date and/or time from character string

Can someone please help on this?

But my real question would be
How can I write a query that should print output when


  1. When one of the AssignedDate from between is null it should print
    all the values excluding char(39)


    1. When both AssignedDate from both the between clause is null it should print all value including char(39)




Up on request I am adding more sample data and expected output:

**Task | AssignedDate**
A | 01/02/2016
A | 02/03/2016
A | 05/04/2016
A | '
A | '


If my condition says:

scenario#1

where AssignedDate not like char(39)
and convert(varchar(10), convert(date,assigneddate, 105),112) >= 20160301
and convert(varchar(10), convert(date,assigneddate, 105),112) <= 20160331
It should print me:
A | 02/03/2016


scenario#2

where AssignedDate not like char(39)
and convert(varchar(10), convert(date,assigneddate, 105),112) >= 20160201
and convert(varchar(10), convert(date,assigneddate, 105),112) <= null


it should print me:

A | 01/02/2016
A | 02/03/2016
A | 05/04/2016


scenario#3

where AssignedDate not like char(39)
and convert(varchar(10), convert(date,assigneddate, 105),112) >= null
and convert(varchar(10), convert(date,assigneddate, 105),112) <= 20160301


it should print me:

A | 01/02/2016
A | 02/03/2016

Answer

Sorry for replying late: I was able to solve the above query by using above helps provided by you all. First thing I did was took the user input for the date using the date picker and used the following variable @AssignedDateFrom and @AssignedDateTo and then finally I used the following query:

where 
  (@AssignedDateFrom is null or 
        (len(assigneddate)<>1 and
         convert(date,substring(assigneddate,1,10),103)>=@AssignedDateFrom
        )
  )
 and 
  (@AssignedDateTo is null or 
       (len(assigneddate)<>1 and
        convert(date,substring(assigneddate,1,10),103)<=@AssignedDateTo
       )
  )