%
INVALIDDATE = DateSerial(0, 0, 0)
adParamInput = 1
adInteger = 3
adDate = 7
' argument : string, expected format dd/mm/yyyy or dd-mm-yyyy or dd.mm.yyyy
' return : Date object, possible INVALIDDATE
function String2Date(s)
Set reo = New RegExp
reo.Pattern = "(\d{2})[/\-\.](\d{2})[/\-\.](\d{4})"
reo.Global = True
Set mco = reo.Execute(s)
if mco.Count = 1 then
Set mo = mco.Item(0)
dd = mo.SubMatches(0)
mm = mo.SubMatches(1)
yyyy = mo.SubMatches(2)
d = CInt(dd)
m = CInt(mm)
y = CInt(yyyy)
if 1 <= d and d <= 31 and 1 <= m and m <= 12 and 2000 <= y and y <= 2050 then
res = DateSerial(y, m, d)
else
res = INVALIDDATE
end if
else
res = INVALIDDATE
end if
Set mo = Nothing
Set mco = Nothing
Set reo = Nothing
String2Date = res
end function
' argument : Date object
' return : satring in format dd/mm/yyyy
function Date2String(dt)
d = Day(dt)
m = Month(dt)
y = Year(dt)
dd = CStr(d)
if Len(dd) = 1 then dd = "0" & dd
mm = CStr(m)
if Len(mm) = 1 then mm = "0" & mm
yyyy = CStr(y)
Date2String = dd & "/" & mm & "/" & yyyy
end function
' test String2Date and Date2String
sub test(s)
d = String2Date(s)
if d <> INVALIDDATE then
Response.Write s & " -> " + Date2String(d) & "
"
else
Response.Write s & " is not a valid date
"
end if
end sub
call test("26/02/2022")
call test("26-02-2022")
call test("26.02.2022")
call test("33/02/2022")
call test("2022-02-26")
call test("123")
call test("ABC")
' insert Date objects in database using parameters and select again from database
Set con = Server.CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Work\Database11.accdb;User Id=admin;Password=;"
Set ins = Server.CreateObject("ADODB.Command")
ins.ActiveConnection = con
ins.CommandText = "INSERT INTO dtest VALUES(@i,@d)"
ins.Parameters.Append(ins.CreateParameter("@i", adInteger, adParamInput))
ins.Parameters.Append(ins.CreateParameter("@d", adDate, adParamInput))
ins.Parameters("@i").Value = 1
ins.Parameters("@d").Value = String2Date("21/02/2022")
ins.Execute
ins.Parameters("@i").Value = 2
ins.Parameters("@d").Value = String2Date("22/02/2022")
ins.Execute
ins.Parameters("@i").Value = 3
ins.Parameters("@d").Value = String2Date("23/02/2022")
ins.Execute
Set ins = Nothing
Set sel = Server.CreateObject("ADODB.Command")
sel.ActiveConnection = con
sel.CommandText = "SELECT * FROM dtest"
Set rs = sel.Execute
Do While Not rs.EOF
Response.Write rs("i") & " " & Date2String(rs("d")) & "
"
rs.MoveNext
Loop
Set rs = Nothing
Set sel = Nothing
Set con = Nothing
%>