biotec
asked on
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 EndDateASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Olaf,
Got it, Why your are giving this
THEN ' ' --> such a big string instead of ''??
Got it, Why your are giving this
THEN ' ' --> such a big string instead of ''??
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.
Bye, Olaf.
No char(10) will take spaces and will creates issues when comparing things . '' should be fine.
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.
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.
ASKER
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.
In some reporting engines you might want to have space(10)
>> May be there was some requirement. But In my cases I used ''.
>> May be there was some requirement. But In my cases I used ''.
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.
Bye, Olaf.
ASKER
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!
Thank you both!
I explained why I choose >= and stay with that recommendation with the expressions I gave.
Bye, Olaf.