tags:

views:

264

answers:

3

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 ...
A: 

Why not use LINQ library? It is very nice and powerful.

What are the typical queries that you are running (high-level pseudocode please).

Hamish Grubijan
the point is, poster has to get it converted before doing anything else with it. LINQ isn't the answer to everything!
Mitch Wheat
Agreed with Mitch, I just want the .net code (therefore linq or whatever) to think it is already dealing with a date field
davidsleeps
+1  A: 

In Oracle, you can use the TO_DATE after you've cast the date_col to a string:

TO_DATE(TO_CHAR(date_col), 'YYYYMMDD')

...or 'YYYYDDMM' for the date format.

References:

OMG Ponies
OMG Ponies, is Oracle RDB 7 the same as the Oracle of now? I thought they were different...
davidsleeps
Just thought i'd add that your solution appears to be working except that it is forcing me to enter extra parameters (wants 3 for each), but I think this is related to the environment, not your solution...e.g. TO_DATE(TO_CHAR(date_col,'00000000','NLS...'),'YYYYMMDD','NLS...') even though they are meant to be optional
davidsleeps
The earliest Oracle I've worked on is 8i.
OMG Ponies
Ok...I'm fairly certain that Oracle X is different to RDB...which was bought by Oracle...
davidsleeps
I'd never heard of RDB before. When I googled RDB 7, I thought it was pre-8i. Luckily the syntax is supported, even if the parameters are non-optional in RDB 7.
OMG Ponies
Rdb was originally a DEC (Digital Equipment Corp) product, purchased by Oracle to get hold of some technology - I believe it was the cost-based optimizer, which Rdb had before Oracle's flagship product. Used it for many years - good product, but tools were limited.
Bob Jarvis
+1  A: 

It's been a while since I had to mess with Rdb. I seem to recall that there was a way to convert to a TIMESTAMP datatype by doing something like

CAST('YYYYMMDDHHMMSSTT' AS TIMESTAMP)

WHERE 'YYYYMMDDHHNNSSTT' was a character string in year-month-day-hour-min-sec-fraction format. You may need to use DATE ANSI here instead of TIMESTAMP - like I say, it's been a while. Regardless, the following might work:

SELECT CAST((CAST(DATE_COL AS CHAR(8)) || '00000000') AS TIMESTAMP)...

which is still ugly but is perhaps better than all the substringing. Anyways, play with it a bit and I'm sure you'll get it.

Bob Jarvis
Ended up using a very similar solution to yours which was CAST(CAST(DATE_COL AS VARCHAR(8)) AS DATE VMS)
davidsleeps
Ah-ha! DATE VMS! I knew it was *something* like that! :-) Glad to help spark your thinking.
Bob Jarvis