SQL query to ignore specific date

I have a query that requires putting in the start and end date for a provider. Problem is there is a bug in the system that is making me put in a fictitious end date of say 01/01/2100 but I need the query for this portion to ignore that particular date and still show it as blank. Trying to figure out in the end_date portion of this how to get it to ignore a specific date. Thanks

convert(varchar, u.effective_date, 101)  as StartDate, convert(varchar, u.end_date, 101) as EndDate

Open in new window

biotecAsked:
Who is Participating?
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
Instead of using u.end_date use CASE WHEN u.end_date>='21000101' THEN NULL ELSE u.end_date END. That'll result in NULL overall.

If you want an empty varchar string, you never get that from convert(varchar, somedatetime,101), but you can apply the idea to the whole value as in

CASE WHEN u.end_date>='21000101' THEN '          ' ELSE convert(varchar, u.end_date, 101) END as EndDate

Open in new window


PS: I choose >= the date you gave, because you said "say 01/01/2100", >= will cover other arbitrary end dates not really meaning their value, as long as that's the minimum date you use for that case.

Bye, Olaf.
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please try this

CASE WHEN convert(varchar, u.end_date, 101) = '01/01/2100' THEN '' ELSE convert(varchar, u.end_date, 101) END EndDate

Do not use > as we are just checking equality as both are text so > will not help here. Also the format is very important here.
0
 
Olaf DoschkeSoftware DeveloperCommented:
@Pawan: Notice I compare the datetime column u.end_date with '21000101', that's not making it a string comparison. Check for yourself.

I explained why I choose >= and stay with that recommendation with the expressions I gave.

Bye, Olaf.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Pawan KumarDatabase ExpertCommented:
Hi Olaf,
Got it, Why your are giving this    

THEN '          '  --> such a big string instead of ''??
0
 
Olaf DoschkeSoftware DeveloperCommented:
Simply to let it have the same length (10). You can use '' I guess and it still will be a varchar(10), you could also choose char(10) anyway, for that date format.

Bye, Olaf.
0
 
Pawan KumarDatabase ExpertCommented:
No  char(10)  will take spaces and will creates issues when comparing things . '' should be fine.
0
 
Olaf DoschkeSoftware DeveloperCommented:
In some reporting engines you might want to have space(10) rather than empty string to have same width as for a concrete date string (eg in monospaced fonts), whatever you think about when you say "issues when comparing things".

I think biotec can figure out, what he wants, he may even want '  /  /   ' or 'unspecified' or anything else making sense in the context using that result.

Bye, Olaf.
0
 
biotecAuthor Commented:
Thanks guys. I'll check both in a few minutes when I get access to the server but a single blank space for the purposes of this report will be fine although good to know the other option.
0
 
Pawan KumarDatabase ExpertCommented:
In some reporting engines you might want to have space(10)
>> May be there was some requirement. But In my cases I used ''.
0
 
Olaf DoschkeSoftware DeveloperCommented:
In any case you'd want to do further queries on these startdate and enddate strings, you'll not be able to compute with them, compare correctly, etc, as they are now string values. The only purpose I can think of for converting to string is for display in that format.

Bye, Olaf.
0
 
biotecAuthor Commented:
I used this CASE WHEN u.end_date >= '21000101' THEN '' ELSE convert(varchar, u.end_date, 101) END EndDate as doing a convert in the CASE statement didn't work but I didn't really give all the detail which is my fault, as with this particular vendor they like to store dates as YYYYMMDD which in this case means I had to do the compare against the data that way.

Thank you both!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.