topic Re: Convert Time Stamp in QlikView

Convert Time Stamp

Wed, 25 Nov 2020 16:16:04 GMT

Hi ALL,

I have this TimeStamp 'Jan 28 2016 14:56:37 GMT' I try to get the month and to do that I procede like this :

1.Timestamp#(Left(start,20),'MMM.DD.YYYY hh:mm:ss') return Jan 28 2016 14:56:37 but when add the monthName or Month function didn't return any data " Month(Floor(Timestamp#(Left(start,20),'MMM.DD.YYYY hh:mm:ss')))as Dat,"

2."Date(Date#(start,'MMM.DD.YYYY hh:mm:ss'),'MMM.DD.YYYY')as Dat" I try this to start by diplay the date then I will get the month but it didn' work too!!!!!!!

3: I try this"Date(Floor((Timestamp#(Left(start,20),'MMM.DD.YYYY hh:mm:ss')))) "as Dat,' but it didn't return any data too:

Help !!!

Re: Convert Time Stamp

sunny_talwar — Sun, 17 Apr 2016 14:36:13 GMT

If you just need Month, you can try this:

Month(Date#(Left('Jan 28 2016 14:56:37 GMT', 3), 'MMM')) as Month

or this to get the MonthNum

Num(Month(Date#(Left('Jan 28 2016 14:56:37 GMT', 3), 'MMM'))) as MonthNum

UPDATE: Also make sure that you correctly read the dateformat when you are using Date# or TimeStamp# functions

This -> Timestamp#(Left(start,20),'MMM.DD.YYYY hh:mm:ss') is not going to work because of the dots you have after MMM and DD (in red). Do you see those in your date? I don't, so why use them in your date formatting. You need the format without the dots like this:

Timestamp#(Left(start,20),'MMM DD YYYY hh:mm:ss') and then you can use your Date, Month, Day, MonthName, Year functions.

Read through these blogs to increase your understanding on how dates are read in QlikView

Get the Dates Right

Why don’t my dates work?

Re: Convert Time Stamp

swuehl — Sun, 17 Apr 2016 14:38:52 GMT

There are not dots in the timestamp format, so try removing them

Month(Timestamp#(Left(start,20),'MMM DD YYYY hh:mm:ss')) as Dat

or just

Month(Timestamp#(Left(start,11),'MMM DD YYYY')) as Dat

Re: Convert Time Stamp

Sun, 17 Apr 2016 16:11:02 GMT

Thank you .

Re: Convert Time Stamp

Sun, 17 Apr 2016 16:11:08 GMT

Thank you .

Re: Convert Time Stamp

oknotsen — Sun, 17 Apr 2016 20:32:56 GMT

If your question is now answered, please flag the Correct Answer.

If not, please let us know with which part of your question you still need help .

Re: Convert Time Stamp

Sun, 17 Apr 2016 20:47:51 GMT

Hi Onno,

I tried the two suggestions and they work well ,

Done for the mention .