read and transforming

[userid128223]

i have field like below. I need to convert it in promper format so i can do calcualtion on it. like convert from one time zone to another.

Date Time:

02/21/2017 22:02:33          //  I see date & time left alligned.

This is not being read as proper format hence i cannot do any conversion.

[ElizaF]

Maybe because the field name is [Date Time] and in expression you used dateTime?

[userid128223]

sorry thats just the title on the field. please ignore that its not an issue.

[MarcoWedel · Accepted solution]

Hi,

left aligned Date Time values indicate text values instead of proper dual timestamp values.

You could try to load this field changing your script like

LOAD TimeStamp#([Date Time],'MM/DD/YYYY hh:mm:ss') as [Date Time]
FROM YourSource ...

hope this helps

regards

Marco

[#5]

Hello,

Check the examples of the Qlik help for date and time functions and do some tests.

You'll be using those functions often in the future.

,KR Koen

[Sergey_Shuklin]

Hello!

Maybe this will help:

Convert TimeStamp format

[ahaahaaha]

Hi

You may use for convert in your date format

TimeStamp(TimeStamp#([Date Time], 'MM/DD/YYYY hh:mm:ss'))  or

Date(TimeStamp#([Date Time], 'MM/DD/YYYY hh:mm:ss'))

[kamal_sanguri]

Pls try this:

=Date(Date#('02/21/2017 22:02:33 ','DD/MM/YYYY HH:MM:SS'),'DD/MM/YYYY HH:MM:SS')

EDIT

1.) Corrected time format

DD/MM/YYYY HH:MM:SS - Incorrect

DD/MM/YYYY hh:mm:ss - Correct

[userid128223]

Your suggestion seems to work in script but when i try it in text box it will not work. Any clue why.

Second question is how can i do a GMT to EST converstion from the date, which is -4 hours.

[kamal_sanguri]

Select only one date after you put this expression in text box. The reason behind this is, if you use a field name it will have multiple values and in normal condition text box can't handle this.

If you still want to use field name without selecting any value , try this way-

=concat([your expression],' ,')

This will concatenate all the dates separated by "," (comma).