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.

[Miguel_Angel_Baeyens]

Hi,

Try in you load script

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

Miguel

[simondachstr]

Try using SubField with 'T' as delimiter.

e.g.

SubField(Header 1,'T',1) as Date

[Colin-Albert]

There's a comma missing after 'T'

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

[Miguel_Angel_Baeyens]

Checked thanks!

[#6]

Hi,

If you want date alone, you can use this coding:

=Date(Left('2011-12-01T08:30:00',10))

Rgds,

Raju

[#7]

Miguel,

This is the error that am getting

SQL##f - SqlState: S1000, ErrorCode: 35, ErrorMsg: [Hortonworks][Hardy] (35) Error from Hive: error code: '40000' error message: 'Error while processing statement: FAILED: ParseException line 1:30 character '#' not supported here

'.

table:

select IntervalTime ,Date(Date#(Replace(IntervalTime, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield   from DailyData

[Miguel_Angel_Baeyens · Accepted solution]

That's right,

I was showing a QlikView function, not a SQL function:

Table:

LOAD

     IntervalTime,

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

SQL SELECT IntervalTime

FROM DailyData;

Always use a LOAD statement in QlikView.

Miguel

[Colin-Albert]

The date expression is a Qlikview function so will need to be in the load section of the script.

table:

LOAD

    IntervalTime,

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

SQL select IntervalTime   from DailyData ;

[preminqlik]

try this

Date(Date#(SubField(Header 1,'T',1),'YYYY-MM-DD'))                    as                    Date

@

Prem