SQL - I have two date strings the return len(9), m/dd/yyyy and mm/d/yyyy. What would help choose the-CodePudding

This is kinda hard to fill in the backstory. I'm using software that only recognizes fixed dates in mm/yyyy or m/yyyy format but it can read linked dates from a table in MS Access. In the program you have the option to convert the Access linked dates to fixed dates stored in the software. When it converts to a fixed value it can write the date in several variations but the one that's giving me trouble is when it writes in either of these two formats "m/dd/yyyy" or "mm/d/yyyy" since both of these return a length of 9. The code below is how I handle the other formats with length 6,7,8, or 10. I only noticed the error with len 9 because I finally had a situation where it wrote the date as "m/dd/yyyy" and it caused errors in the software because my query "corrected" the date into "m//yyyy" format. I have no control of how it writes the date. The software company knows about the issue and is addressing it in the next update but I have no idea when that will rollout so I need a workaround until then.

Using the method below will return either "m//yyyy" or "mm/yyyy". How can I make it choose to handle the "m/dd/yyyy" format to output "m/yyyy" instead of having the two backslashes? This isn't my code, someone on here wrote it in response to a previous question. I'm not very competent in any programming language but I have a basic understanding of functions.

IIf(Len([Expression])=6,"0" & [Expression],
IIf(Len([Expression])=7,[Expression],
IIf(Len([expression])=8,Left([Expression],1) & "/" & Right([expression],4),
IIf(Len([expression])=9,Left([Expression],2) & "/" & Right([expression],4),
IIf(Len([expression])=10,Left([Expression],2) & "/" & Right([expression],4))))))

My guess was to include a nested AND function or something like this but I don't know how to get that to work properly and then I'd have to do something like an IfElse statement to handle the "mm/d/yyyy" format. I'm sure there's a more efficient way to get to the solution and I would appreciate the help from someone who actually knows what they are doing instead of me fumbling through it and banging my head on my desk for the next month.

If Len[Expression]=9 AND Left[Expression],2 = "/", Left([Expression],1) & "/" & Right([Expression],4) Else Left([Expression],2) & "/" & Right([Expression],4)

CodePudding user response：

Try adding a Replace function for items that are 9 chars long:

IIf(Len([expression])=9,Replace(Left([Expression],2) & "/" & Right([expression],4), "//","/"),

CodePudding user response：

Always handle date and time as Date, not Text, not numbers, no exceptions.

So, format your true date value (the value you can read) to the required text format:

TextMonthYear = Format([YourTrueDate], "mm\/yyyy")