asked
Add a hyphen '-'
I need to be able to add a hyphen '-' to the end of a date column based on certain criteria. If field3 has more then one 'VISIT' value. Based on account number. Each account number is broken down into detail lines. **(I can't have hyphens for all the dates in this field.)** So for example there may be 5 of the same account number in the table and in the detaillines field it would be numbered 1-5 based on that account number. What is an effective way of doing this??? Function?? If so could I get an example? Sorry if this is to vague, please let me know if it is and I will try to make it better. *** 00/00/00 *** 00/00/00 to this *** 00/00/00 **-** *** 00/00/00 Each line representing a row. Based on the above criteria.
The real problem is, it sounds like you're overloading the column. If it's a date column, store dates there. If you need another column to act as a flag, add another column. The date column should be of the date or datetime data type. You won't then be able to add this hyphen to it, if the data is stored correctly.
Excellent point, Grant. Sometimes, especially when I don't have a lot of time, I focus so much on trying to help solve the problem that I forget to ask myself why. I completely agree that the desire to mix a date and flag in one column is flawed, and a better approach would be to use 2 columns.
But, I like your solution and voted it up. Nothing wrong with answering the question asked. I just think it's important to assess the underlying structures of the question too. Your answer is quite solid. That's why I just commented rather than adding another answer. Yours works.
Can you paste a copy of your SELECT statement? Also, how do you know if Field3 has more than 1 value? Is it a delimited string of values stored in 1 field? If so, it sounds like you might just need a CASE for that column. Let's say Field3 can be a single value or a comma-delimited list with multiple values. In that case, something like this might work: DateField = DateValue + CASE WHEN CHARINDEX(',', Field3, 1) > 0 THEN ' - ' ELSE '' END If my guesses were wrong, I can change my answer after I have your answers to my questions.
CASE WHEN Field3 = 'VISIT' AND TRY_PARSE(SUBSTRING(DescriptionField,1,8) AS datetime) IS NOT NULL THEN CONVERT(VARCHAR(8), DateField, 1) + '-' ELSE CONVERT(VARCHAR(8), DateField, 1) END AS WowThisTookForever... Soooooooo this is what ended up working. For reasons I can't explain here they needed that hyphen in that date field. This is hands down the funkiest CASE statement I have ever written. Thanks for your help again!!!
17 People are following this question.
