Date Conversion (ugg I hate this)

[JDionne]

ok i have a date field that comes to me looking like 031023 and I need it to look like 2003-10-23 what is the best way to do this?
Jim

[kcbland]

Look here for some date math:

viewtopic.php?t=85788

The quick answer is to learn ICONV and OCONV.

Code: Select all

NewDt = OCONV(ICONV(OldDt, "DYMD[2,2,2]"), "D-YMD[4,2,2,]"

[kcbland]

Here's a handy-dandy function that attempts to recognize any date format and turn it in to the internal date number.

You then can externalize it however you want.

I call this function KBADateIconv, but you can call it whatever you want:

Code: Select all

      Date = Arg1
      IntDate = ICONV(Date,"D")
      If IntDate = "" Then
         CONVERT "/ \:.,-_" TO "--------" IN Date
         Begin Case
            Case Date MATCHES '0n"-"0n"-"0n'
               yy = Field(Date,"-",1)
               mm = Field(Date,"-",2)
               dd = Field(Date,"-",3)
               IntDate = ICONV(mm:"-":dd:"-":yy,"D")
            Case LEFT(Date,10) MATCHES '4n-2n-2n'
               Date = LEFT(Date,10)
               yy = Field(Date,"-",1)
               mm = Field(Date,"-",2)
               dd = Field(Date,"-",3)
               IntDate = ICONV(mm:"-":dd:"-":yy,"D")
            CASE @TRUE
               IntDate = ""
         End Case
      End
      Ans = IntDate

[kcbland]

Continuing with the previous post, you can then use KBADateIconv in any function and format the output however you like:

I call this one KBADateToOracleTimestamp:

Code: Select all

      DEFFUN KBADateIconv(A) Calling "DSU.KBADateIconv"

      Ans = OCONV(KBADateIconv(Arg1), "D-YMD[4,2,2]"):" 00:00:00"

[ray.wurlod]

Unless, like much of the world outside the USA, you're in a locale that uses DMY or YMD as the default date order. I have a better one back at home, which I will post after I return from Thailand.
Basically, it does an OCONV to test the local order, and does the generic conversion on this basis.