I'm working on a .Net WinForms appliation that is connecting to a legacy RDB database...
Some of the fields for dates are stored as integers (imagine 2010-01-04 was the integer 20100104)
In the .Net world, I'd much rather work with datetime objects and I've been able to convert the integer into a date, just that it looks so ugly, takes up lots of lines, is prone to errors and I'm wondering if anyone would be able to improve it...Thanks heaps!
Note - I cannot edit the database so creating any form of "function" is out of the question...
Here's my current way of selecting the integer as a datetime:
select
CAST(
SUBSTRING(DATE_COL AS VARCHAR(8)) FROM 1 FOR 4) ||
'-' ||
SUBSTRING(CAST(DATE_COL) AS VARCHAR(8)) FROM 5 FOR 2) ||
'-' ||
SUBSTRING(CAST(DATE_COL) AS VARCHAR(8)) FROM 7 FOR 2) ||
' 00:00:00'
AS TIMESTAMP) AS DATE_COL
from MY_TABLE
where ...