Date conversion

General
1

In a imported SQLite column I have a value i.e.: “12/01/09 00:00:00”. I want to convert this to a correct SQLlite date:
The day is 01, month = 12 and year = 09
I executed this code:

[code] dim newDate as new Date
aStringDate = rs.Field(“P_DOB”).StringValue
if aStringDate <> “” then

      theDay = Mid(aStringDate,4,2)
      theMonth = Mid(aStringDate,1,2)
      theYear = Mid(aStringDate,7,2)
      newDate.day = Val(theDay)
      newDate.Month = val(theMonth)
      newDate.Year = val(theYear)
    end if
    rs.Field("P_DOB").DateValue = newDate[/code]

But the newDate value = Jan 01, 1601

What gets wrong here?

2

The Year should have 4 digits, you only get 2 from your parsing. You should add “20”:

theYear = "20"+ Mid(aStringDate,7,2)

Granted it is a year from the 21 century ; -)

3

Furthermore,

theDay = Mid(aStringDate,4,2)

should be

theDay = Mid(aStringDate,3,2)
4

[quote=83173:@Paul Sondervan]Furthermore,

theDay = Mid(aStringDate,4,2)

should be

theDay = Mid(aStringDate,3,2)

Hmm I’m not sure about that, Paul. I think

theDay = Mid(aStringDate,4,2)

is correct granted the original string is:

"12/01/09 00:00:00"
5

Sorry, I must have had dust in my eyes. :blush:
You are right Guy.

6

Also, make sure you assign the values in Year, Month, Day format. If the day being converted is january 31st, but you’re doing it in February, you’ll end up with March 3rd because the days will roll over.

Another way to handle this would be to create the date using the date constructor:

NewDate = New Date(theYear, theMonth, TheDay)
7