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 ...
MT0
  • 143,790
  • 11
  • 59
  • 117
davidsleeps
  • 9,393
  • 11
  • 59
  • 73

3 Answers3

3

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 - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
1

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
  • 325,700
  • 82
  • 523
  • 502
  • OMG Ponies, is Oracle RDB 7 the same as the Oracle of now? I thought they were different... – davidsleeps Jan 04 '10 at 03:23
  • 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 Jan 04 '10 at 03:25
  • Ok...I'm fairly certain that Oracle X is different to RDB...which was bought by Oracle... – davidsleeps Jan 04 '10 at 03:42
  • 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 Jan 04 '10 at 04:02
  • 3
    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 - Слава Україні Jan 04 '10 at 12:47
0

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
  • 10,562
  • 23
  • 99
  • 147
  • 1
    the point is, poster has to get it converted before doing anything else with it. LINQ isn't the answer to everything! – Mitch Wheat Jan 04 '10 at 02:19
  • 1
    Agreed with Mitch, I just want the .net code (therefore linq or whatever) to think it is already dealing with a date field – davidsleeps Jan 04 '10 at 02:47