3

In , Australian dates in SQL statements are delimited and formatted as #d/m/yyyy#. For example:

SELECT * FROM mytable WHERE mydate BETWEEN #2/1/2001# AND #4/3/2001# AND more...

I need to grab this SQL statement and massage it so I can present it to SQLite as:

SELECT * FROM mytable WHERE mydate BETWEEN '2001-01-02' AND '2001-03-04' AND more...

The task, then, is to convert #xd/xm/yyyy# to 'yyyy-mm-dd'. The "Hello, World!" of regex examples.

Using with .Global = TRUE, I'm able to replace the delimiters and re-order the elements in one step:

vOldPattern = "#([0-9]{1,2})/([0-9]{1,2})/([0-9]{4})#"
vNewPattern = "'$3-$2-$1'"

This gives the following intermediate result:

SELECT * FROM mytable WHERE mydate BETWEEN '2001-1-2' AND '2001-3-4' AND more...

I thought I could pad all the single digits with a single pass:

vOldPattern = "-(\d[-'])"
vNewPattern = "-0$1"

but this approach padded only the first (month) digit. I gave up and ran through two passes, matching first on "-(\d-)"

and then on "-(\d')"

So, three passes to alter the format and tweak the content. Surely there's a single search/replace command for this task. Can somebody volunteer a more elegant solution?

And, since I have to compile and export the runtime, I guess I can't use late binding but instead need to load the reference to the MS VBScript Regular Expressions. I'm developing in Win7/32, but the app needs to be backwards-compatible (compatible?) with WinXP. Which version (1.0 or 5.5) of the VBScript RegEx library should I load?

Cheers!

Improve this question
1
  • I'm not familiar with VBA's regex pattern. Can you use vOldPattern="-(\d\b)", vNewPattern="-0$1" in your first (single pass) attempt? Commented Oct 4, 2012 at 7:17

4 Answers 4

Reset to default
1

You can use a positive lookahead to get around padding only the first zero - which I think occurred for you as the last - is consumed in testing for a non-digit for your second -

So it can be reduced to two patterns as below

I don't think it can be done in a single shot but I'd like to see it.

I used
2/12/2001
rather than
2/1/2001
to prove the replacement handles mixed strings

Sub Test()
Dim strIn As String
Dim objRegex As Object
strIn = "SELECT * FROM mytable WHERE mydate BETWEEN #2/12/2001# AND #4/3/2001# AND more"
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.Pattern = "#([0-9]{1,2})/([0-9]{1,2})/([0-9]{4})#"
strIn = .Replace(strIn, ("$3-$2-$1"))
.Pattern = "(-)(\d)(?=[^\d])"
strIn = .Replace(strIn, "$10$2")
MsgBox strIn
End With
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks, Brett. The positive look-ahead works swell to reduce the process from three to two passes. I simplified the second pass just slightly. I can't post it here so I'll answer my own question, but give you all the glory. Cheers!
+1 for regex, they make VBA a good sport among those super duper cousins of it like c# .net... ;)
0

I believe you could perform this entire task with the Format() function.

If not, you could use Format() to put the leading zeros in. Something like this:

vNewPattern = _
    "'" & Format("$3","00") & "-" & Format("$2","00") & "-" & Format("$1","00")

Admittedly, it is not elegant. However, it is clear, and it should be just as efficient.

Improve this answer

Comments

0

Regular expressions are not necessary. Just use the format() function:

Dim myDate As Date
myDate = #4/3/2001#
Debug.Print Format(myDate, "yyyy-mm-dd")
Improve this answer

4 Comments

I think you missed that the question is parsing and formatting a string - it isn't taking a date as an input.
Ooops, yes - I've just re-read the question. I thought he needs to take the resulting dates and massage them. Sorry for that.
However - just re-re-reading it: his date values (#...#) must come from somewhere - they won't be hardcoded, will they? So he may well be able to use format in his re-formatted statement.
The WHERE clause is assigned directly from a recordset filter, so I have no control over the date formatting. I'm trying to tweak any embedded dates without deconstructing (or even inspecting) the entire filter string. So, in this case, I think regex is the way to go. But thanks anyway. I'd forgotten about Format(), and may have use for it elsewhere in my code. Cheers!
0

Brett's answer above is elegant enough for me. I simplified the syntax of the second pass just slightly, but left Brett's solution as comments.

Sub Test()
Dim strIn As String
Dim objRegex As Object
strIn = "SELECT * FROM mytable WHERE mydate BETWEEN #2/12/2001# AND #4/3/2001# AND more"
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.Pattern = "#([0-9]{1,2})/([0-9]{1,2})/([0-9]{4})#"
strIn = .Replace(strIn, ("'$3-$2-$1'"))
'.Pattern = "(-)(\d)(?=[^\d])"
'strIn = .Replace(strIn, "$10$2")
.Pattern = "-(\d)(?=[^\d])"
strIn = .Replace(strIn, "-0$1")
MsgBox strIn
End With
End Sub

brettdj and I would both love to see a single-pass solution to this problem. I bet we could do it in good ol' perl!

The second part of my original question was which version (1.0 or 5.5) of the VBScript RegEx library I should register so that I could generate a runtime application. I pulled the trigger and selected just the 5.5. version (has it REALLY been around since 1999?). The application compiles successfully. If it doesn't run as a stand-alone, I'll let you know.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.