How to convert a six-digit numeric to a date/time field?

[cjkoontz]

I have a six-digit date field (BPADAT) defined as:
* Data Type Number,
* Field Size Number,
* Precision 6,
* Scale 0
(in YYMMDD format).

I'm looking for the simplest way to convert the date in a query to a Date/Time field (BPADATDT).

Unfortunately, the simplest way that I can think possible is to do the following:

BPADATDT: CVDate(Mid(Str([BPADAT]),3,2) & "/" & Mid(Str([BPADAT]),5,2) & "/" & Mid(Str([BPADAT]),1,2))

Is there a simpler method?

 

[cheerio]

Given the nature of your original data any process is going to be messy.

As your source data is a number you could avoid all the string operations. It should be possible to separate out the year, month and day as numbers and then create the date with the DateSerial function. The separation could be a combination of the use of the MOD function and the \ operator.

However, I am not convinced it would be simpler or more efficient - just an exercise in the use of those operations.

So stick with your solution.

 

[JeremyNYC]

I'm not sure, but I would guess the DateSerial function would be faster, if you've got a lot of records.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[cjkoontz]

I came-up with this to handle the cases where the six-digit field is zero and when there are less than six significant digits:

BPADATDT: Format(IIf([BPADAT]=0,Null,CVDate(Mid(Format([BPADAT],"000000&quot,3,2) & "/" & Mid(Format([BPADAT],"000000&quot,5,2) & "/" & Mid(Format([BPADAT],"000000&quot,1,2))),"yyyy/mm/dd&quot