Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi,
Try in you load script
Date(Date#(Replace(FieldNameHere, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield
Miguel
Try using SubField with 'T' as delimiter.
e.g.
SubField(Header 1,'T',1) as Date
There's a comma missing after 'T'
Date(Date#(Replace(FieldNameHere, 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) AS Datefield
Checked thanks!
Hi,
If you want date alone, you can use this coding:
=Date(Left('2011-12-01T08:30:00',10))
Rgds,
Raju
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
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
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 ;
try this
Date(Date#(SubField(Header 1,'T',1),'YYYY-MM-DD')) as Date
@
Prem