Date conversion

[maxtektips6]

Hi,

How do i convert a string like 2007-14-11T11:45:47.0+0100 into a date ?

I've tried the follwoing:

SQL> select to_date(f1,'yyyy-dd-mmTHH24:MI:SS.S+0100') from mx_tmp;
select to_date(f1,'yyyy-dd-mmTHH24:MI:SS.S+0100') from mx_tmp
                  *
ERROR at line 1:
ORA-01821: date format not recognized

 

[maxtektips6]

Forgot to add: i can't use substr to just take the date - as i don't actually need the time part that comes after the "T" character-; that's because this has to run in a timesten database which has limited oracle functionalities.

 

[hoinz]

Will the letter 'T' always be a 'T' ?
And will the '.0+0100' at the end always be the same?
And may we forget about it, or is it needed for conversion between timezones?

What about this:
select to_date(f1,'yyyy-dd-mm"T"HH24:MI:SS".0+0100"') from mx_tmp;

 

[maxtektips6]

Thanks Hoinz. Your suggestion runs indeed fine in Oracle but doesn't in the timesten table:

Yes the T is always there. Unfortunately what comes after the "." changes so apart from the T and the punctuation everything else is variable in that column.

Command> select to_date(start_time,'yyyy-dd-mm"T"HH24:MI:SS".0+0100"') from mx_tmp;
< 2007-11-13 00:26:36.000000 >
 2608: Error converting from character string '+0100' to timestamp
1 row found.
The command failed.

 

[hoinz]

ok then; a few other questions:
It seems you have got fractal parts of a second; they will not fit into Oracle's date format. May we forget the fractals? (If not, you might have to consider a different data type.)
And what about the '+0100' part? May we forget it, even if it is not the same?
If the answer to all questions is 'yes', you may use substr() to get rid of all what comes after the seconds.
Am I right in assuming that the '+0100' part means the timezone? And do you need it for your select?

 

[hoinz]

After a bit more thinking about it:

If you can afford to omit the tenths of seconds, but have to take into consideration the timezone, and want to convert to GMT, take this approach:
Use substr() and a format similar to the one shown above, to convert '2007-14-11T11:45:47' to date.
Then use another substr() to cut out the '+0100' part, convert to_number(), convert to days (by dividing /100/24), and add the days to your first date.
(Or perhaps you will have to subtract the days; try it out!)

(As you perhaps know: If you add 1 to a date, you will get 1 day later; if you add 1/24, you will get 1 hour later.)

hope this helps

 

[maxtektips6]

I wish i could do anything about the data types. It's just given, can't change it. This format is indeed providing dates with a precision to the tenth of second and the time is UTC. What comes after the + sign is the offset from GMT. And "substr" is not available .. a pain to have such limited functions available with such exotic date formats if you ask me. But that's how tricky the situation is.

Well, i was just about to add that my aim was to write a statement to select rows older than a certain day or a certain amount days when the following possibility came to me:

Command> select count(start_time) from TABLE_XYZ where start_time < '2007-12-01T00:00:00.0+0100';
< 77739 >
1 row found.

So it looks like implicit conversions do the trick. Thanks for helping Hoinz.

 

[hoinz]

Well, I don't know anything about timesten, and I think I prefer not to know ...

One more remark: Your idea of using 'where start_time < ...' seems good in this case. But there is no implicit conversion. It is just comparison of strings, like 'a' < 'b'.
And so there might be problems in certain cases, depending on your data.

Which is the older of these two strings:
'2007-12-01T00:00:00.0+0100'
'2007-12-01T05:00:00.0+1000'

String comparison will say the first, because 'T00:00' < 'T05:00'
But regarding GMT, the first was one hour before midnight, whereas the second was five hours before midnight.


Good luck,
I have to leave now!

 

[maxtektips6]

Yep you're entirely correct, there is no conversion at all taking place. It's definitely not the best solution but it'll do until i can get my hand on some proper documentation.

Cheers