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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase 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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.