topic Re: Different date formats in the same field in QlikView

Different date formats in the same field

Anonymous — Mon, 10 Apr 2017 07:26:17 GMT

Hello, I have all these date formats in the same field and I wanted to know if anyone knows any way to format them in any way, whether it's just staying with day, month, year, no time, or including hours.

date

01031760 0924: 49

01032017 09:10

01032017 09:10:52 a

01032017 9:12 AM

01032017 9:16

03/03/2017 8:55:03

2017-03-01 09: 00: 09.0

01032017

010320170906

Thank you very much!

Re: Different date formats in the same field

MK_QSL — Mon, 10 Apr 2017 07:57:05 GMT

You have to use Alt Function to handle multiple data and time formats.

Load

DateTime,

TimeStamp(

Alt

(

TimeStamp#(DateTime,'DDMMYYYY hhmm: ss'),

TimeStamp#(DateTime,'DDMMYYYY hh:mm'),

TimeStamp#(DateTime,'DDMMYYYY h:mm'),

TimeStamp#(DateTime,'DDMMYYYY hh:mm tt'),

TimeStamp#(DateTime,'DDMMYYYY h:mm tt'),

TimeStamp#(DateTime,'DDMMYYYY h:mm:ss t'),

TimeStamp#(DateTime,'DDMMYYYY h:mm t'),

TimeStamp#(DateTime,'DD/MM/YYYY hh:mm:ss'),

TimeStamp#(DateTime,'DD/MM/YYYY h:mm:ss'),

TImeStamp#(DateTime,'DDMMYYYYhhmm'),

Date#(DateTime,'DDMMYYYY')

)

as NewDateTime

Re: Different date formats in the same field

Anonymous — Mon, 10 Apr 2017 08:39:28 GMT

Thanks for your answer, but the problem I have is the date whose format includes a 60. There is no data for 1760, actually the correct date is 01/03/17, but I do not know what the 60 means. That's what I want Format as the rest.

01031760 0924: 49 --> 01/03/17

I hope you can help me, thank you!

Re: Different date formats in the same field

MK_QSL — Mon, 10 Apr 2017 08:45:15 GMT

So you want only Date or DateTime as well.

Re: Different date formats in the same field

Anonymous — Mon, 10 Apr 2017 09:23:52 GMT

Thank you very much for yuor help and your interest.

I just want to show the date. The problem is that all dates appear with the year with four digits, but the one that shows me the 60, the year only has two digits and so it shows it as 1760, but that year does not exist in the database.

Many thanks again, see if we can finally get it out

Re: Different date formats in the same field

MK_QSL — Mon, 10 Apr 2017 09:38:59 GMT

No it's difficult to handle like this..