convert UNIX time to SQL readable date

[jaykjoe]

I'm having an issue trying to convert the UNIX time value to a readable SQL date. I keep getting a "ORA-00904: invalid column name" message. Here's the statement:

SVRMGR> select dateadd(second,f_createtime,'1970-01-01') from f_sw.vwqueue1_127;
select dateadd(second,f_createtime,'1970-01-01') from f_sw.vwqueue1_127
*
ORA-00904: invalid column name
SVRMGR>

If I just a select f_createtime from f_sw.vwqueue1_127, I will get the UNIX time (1119245684) in seconds from 01/01/1970. Anyone help me with what the problem is? I'm running Oracle 8.1.7 if that makes a difference. Thanks.

 

[vongrunt]

IIRC interval identifier in Oracle must be in single quotes ('second' or 'ss').

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jaykjoe]

vongrunt: I've tried that and the problem still exists.

SVRMGR> select dateadd('second',f_createtime,'1970-01-01') from f_sw.vwqueue1_127;
select dateadd('second',f_createtime,'1970-01-01') from f_sw.vwqueue1_127
*
ORA-00904: invalid column name

AND

SVRMGR> select dateadd('ss',f_createtime,'1970-01-01') from f_sw.vwqueue1_127;
select dateadd('ss',f_createtime,'1970-01-01') from f_sw.vwqueue1_127
*
ORA-00904: invalid column name

Thanks for your suggestion though.

 

[r937]

does oracle even have a DATEADD function?

i'm wondering why you chose to post this question in the microsoft sql server forum...

r937.com | rudy.ca