question

g_yerden avatar image
g_yerden 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.
datetimefunctionsstring
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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.
0 Likes 0 ·
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.
0 Likes 0 ·

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This question wasn't the best. Once I figure out what I need to do I will post it. This is a good answer though. Sorry I haven't responded sooner, this problem is making my head spin for business reasons I can't post here. Thank you and Grant for your time.
0 Likes 0 ·
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!!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.