SQL Select & Epoch time questions...

[#1]

Hi,

I have a database that I am using an ODBC connection to connect to.  I can connect to it and pull data.  Now i am doing some data modeling and trying to reduce the amount of data and runtime via applying a where clause on a date field.  The date field is in epoch time.  I would like to set a variable to convert the epoch time to regular time in the script so if I have to use it again, I can simply refer to the variable name.  I have the script below, but it returns 'Not a valid month', ORA-01843 .  I have tried several variations such as

$(SubmitDate) > '01/01/2013'

$(SubmitDate) > '01/01/2013 12:00:00'

$(SubmitDate) > '01/01/2013 12:00:00 AM'

No dice.  Could someone help me out with syntax here?

ODBC

(connection string)

set SubmitDate = (to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (("SUBMIT_DATE"- 4*3600)/(60*60*24)));

SQL SELECT "ASSIGNED_GROUP",
ASSIGNEE,
"FULL_NAME",
"INCIDENT_NUMBER",
STATUS,
"SUBMIT_DATE",
(to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (("SUBMIT_DATE"- 4*3600)/(60*60*24))) as SubmitDateTime

FROM ITSMTST."HPD_HELP_DESK_CLASSIC"

where $(SubmitDate) > '1/1/2013';

[maxgro]

I don't have your table, but this for me works

In submit_date column I have a constant 915148798, just to try your script

Hope it helps you.

set SubmitDate = (to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + ((submit_date - 4*3600)/(60*60*24)));

SQL

select

$(SubmitDate),

to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') 

from dwh_dl.a

where

$(SubmitDate) > to_date('01/01/1950', 'MM/DD/YYYY')

;

[Clever_Anjos]

It´s a very commom problem while using ORACLE.

It´s a conversion problem

Oracle expects a date into YYYY-MM-DD format OR you tranform your string using TO_DATE function

I would to

SQL SELECT "ASSIGNED_GROUP",
ASSIGNEE,
"FULL_NAME",
"INCIDENT_NUMBER",
STATUS,
"SUBMIT_DATE",
(to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (("SUBMIT_DATE"- 4*3600)/(60*60*24))) as SubmitDateTime

FROM ITSMTST."HPD_HELP_DESK_CLASSIC"

where $(SubmitDate) > '2013-01-01';