Replace

[#1]

Header 1
2011-12-01T08:30:00
2011-12-01T09:00:00

My date field is like this...I need to get the date alone from this. I cant use DATE function here because of that "T'.

Can i use replace function for this? But don't know how to change all the values of a field..Please help.

[#2]

Albert,

Am again getting error message like this

Field not found - <IntervalTime>

SQL select IntervalTime from DailyData

[Miguel_Angel_Baeyens]

Is IntervalTime a valid field within DailyData database? Are both actual names?

[#4]

Am using hadoop hive, and yes the table does have the column with the same name (it is working when doing a simple select)

[Miguel_Angel_Baeyens]

Is your driver case sensitive? If it is, you should spell the field as it is stored in the database.

If you are connecting to Hadoop I guess you already have the driver set up properly. Does it work if you do any other select?

[#6]

Hi Menon,

Try this,

Dev:

LOAD * Inline

[

Sal_Date

2011-12-01T08:30:00

];

LOAD SubField(Sal_Date,'T',1) as Sub_DT Resident Dev;

LOAD Date(SubField(Sal_Date,'T',1),'DD-MM-YYYY HH:mm:SS')as Sub_Con_DT Resident Dev;

LOAD Replace(Sal_Date,'T',' ') as rpl_DT Resident Dev;

[#7]

Yes i did the driver set up properly..

it works when i used select * from DailyData

[brijesh1991]

Date(Left(Date, 10) & ' ' & right(Date,8),'DD/MM/YYYY hh:mm:ss') as Date

Put below in a textbox and you can verify::

Date(Left('2011-12-01T08:30:00', 10) & ' ' & right('2011-12-01T08:30:00',8),'DD/MM/YYYY hh:mm:ss')

[#9]

It worked, don't know what's d issue, but giving alias to column name did the trick..Thank u guys..

LOAD

        Date(Date#(Replace(Datefield, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield ;

SQL select IntervalTime as Datefield  from DailyData ;