converting an integer field into a date

[acent]

Greetings,

I'm working with a proprietary database system that I connect to over ODBC.

This proprietary database stores dates as integers (don't blame me, I didn't design it). So the date 1/11/2010 would be stored as 1100111. When you add 1100111 and 19000000 you get year month date - the method to the madness.

What I need to do is create a query that gives me all the records for this month, and 5 months after. The bigger picture is I'm creating a TRANSFORM query to do a crosstab report. Working with dates is easy, but these are not dates, they're integers.

Any help is appreciated.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[SkipVought]

Hi,

Well you add the two value and you do get an integral number, in this case, 20100111.

But you cannot hand this as a single value in the conversion. The conversion will depend on your proprietary system's date system, the syntax for extracting left, mid and right values.

Two that I am familiar with...

Select TO_DATE
 (
   SUBSTR([YourDate]+19000000,1,4)||'/'||
   SUBSTR([YourDate]+19000000,5,2)||'/'||
   SUBSTR([YourDate]+19000000,7,2),'yyyy/mm/dd'
 )

Select DateSerial
 (
   MID([YourDate]+19000000,1,4),
   MID([YourDate]+19000000,5,2),
   MID([YourDate]+19000000,7,2)
 )

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]