Conversion between date formats for textual representation of dates

[mmilan]

Not really got time to document this, but here's a quick and cheerful function I knocked up for converting between date formats...

I'm surprised VB doesn't include something like this (he said, setting himself up for the fall...)

Public Function ConvertDate(sDate As String, Optional sNewFormat As String = "yyyymmdd", Optional ByVal sOldFormat As String = "ddmmyy")

  Dim dtDate As Date
  Dim iYear As Integer
  Dim iMonth As Integer
  Dim iDay As Integer
  Dim iPos As Integer
  
  sOldFormat = UCase(sOldFormat)
  
  'Start working on the old date. First of all, let's dump any CCYY
  sOldFormat = VBA.Replace(sOldFormat, "CCYY", "YYYY")
  'Now try and grab a year.
  iPos = InStr(sOldFormat, "YYYY")
  If iPos > 0 Then
    'Century included.
    iYear = Val(Mid(sDate, iPos, 4))
  Else
    iPos = InStr(sOldFormat, "YY")
    If iPos > 0 Then
      iYear = Val(Mid(sDate, iPos, 2))
      If iYear < 30 Then
        iYear = iYear + 2000
      Else
        iYear = iYear + 1900
      End If
    End If
  End If
  
  iPos = InStr(sOldFormat, "MM")
  If iPos > 0 Then
    iMonth = Val(Mid(sDate, iPos, 2))
  End If
  
  iPos = InStr(sOldFormat, "DD")
  If iPos > 0 Then
    iDay = Val(Mid(sDate, iPos, 2))
  End If
  
  If (iDay > 0) And (iMonth > 0) And (iYear > 0) Then
    dtDate = DateSerial(iYear, iMonth, iDay)
    ConvertDate = Format(dtDate, sNewFormat)
  End If

End Function

Hope someone finds that useful...

mmilan

 

[SkipVought]

mmilan,

...converting between date formats...

These are NOT different DATE formats.

You got sooooooooo close and then booted it

[b]
    dtDate = DateSerial(iYear, iMonth, iDay)[red][s]
    ConvertDate = Format(dtDate, sNewFormat)[/s][/red][/b]

The place where users get all screwed up is using TEXT as dates.

Your ConvertDate comes out as TEXT and NOT a REAL DATE!

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[mmilan]

Actually, YOU'RE wrong - but it's all my own fault. Permit me to explain...

The whole purpose the the function is to convert a textual representation of a date in one format into a textual representation of a date in another. I wrote the function as part of an exercise I am concerned with in importing data between two seperate systems.

This would however have been a lot clearer had I taken a moment to explain myself, and I've also just noticed that I haven't declared a variable type for the return result, which perhaps would have solved some of the mystery had I actually been awake when writing it. It should of course read be String.

I assure you that I am quite well versed in data types and converting between them - just didn't explain what I was doing clearly enough, did I?

And before you ask, yes, I agree that it's better to store dates in date variables - but that would not be suitable for the task I am concerned with...

Hope we're all on the straight and narrow now...

mmilan

 

[JeffTullin]

I recently did some work in T-SQL
It has a CONVERT function that allows you to pass string-y dates and convert them to real dates, and vice versa.
The reason I mention this is that it allows you to specify what the format of the string you supply actually is.

The beauty of this is that the function does not have to guess how the string is formatted.
VB's CDate makes a good stab at this sort of thing, but can easily get it wrong on a system with a different date format.
(English UK vs English USA is the classic example - what exactly is the date if someone gives you 10-08-2004 ?
Americans have this as the eighth of October, but on an British machine it is the 10 of August.)

The function at the beginning of this thread would appear to be a take on this kind of conversion.

 

[strongm]

Um - CDate takes regional settings on the PC into account, you know. It only really gets upset if you enter a date ina different/wrong format for the region the PC is set for.

 

[mmilan]

Yes, I know about convert in MSSQL...

I did consider making use of it, but given the (quite varied) formats that the muppets are choosing to save dates in the flat file, it just wasn't up to the task. I therefore took the approach of being damned sure (hence function) that I had the date in "yyyymmdd", and then moving with that.

The people who supplied to original file were kind enough to state(usually) which format they would use for particular fields (Yes, it varies between fields - even fields on the same table!), they just neglected to restrict themselves to 'conventional' choices...

Again though, my apols... Much of the misunderstanding here was caused by my own laziness in not explaining what I was up to.

Thanks for the suggestions though... You'd be surprised how often I read something on tek-tips and think "That's a bally good idea - I could mangle that for what I'm doing..."

mmilan