How to improve the next SQL pick date in RDB

Question

I am working on a .Net WinForms application that connects to an old RDB database ...

Some fields for dates are stored as integers (suppose 2010-01-04 was an integer 20100104)

In the .Net world, I would rather work with datetime objects, and I was able to convert an integer to a date that just looks so ugly, takes up a lot of lines, is error prone and I wonder if anyone can improve it ... Thanks, heaps!

Note. I can’t edit the database, so creating any form of “function” is out of the question ...

Here is my current way of choosing an integer as 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 ...

Answer 1

, Rdb. , , , TIMESTAMP, -

CAST('YYYYMMDDHHMMSSTT' AS TIMESTAMP)

WHERE 'YYYYMMDDHHNNSSTT' ​​ year-month-day-hour-min-sec-fraction. , DATE ANSI TIMESTAMP - , . , :

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

- , , , , . , , , .

Answer 2

Oracle TO_DATE , date_col :

TO_DATE(TO_CHAR(date_col), 'YYYYMMDD')

... 'YYYYDDMM' .

:

• TO_DATE
• TO_CHAR

Answer 3

LINQ? .

, (, ).