A function to check if a field value is correct date?

[iliyansomlev]

Hi,

I have a string like 20030934 and another like 19871105 and another like 20030229. Obviously only the second is a correct date. Is there a function in qlikview like IsDate(20030934)  to check if a value is correct date? What is the best way to do this check?

Best,

Iliyan

[pennetzdorfer]

isnull(date(date#(20030933,'YYYYMMDD'),'YYYY-MM-DD'))

That returns 0 for valid an -1 for invalid dates.

Regards,

Florian

[kaushiknsolanki]

Hi,

     If the data coming from the source is in date format then qlikview will convert in date format and if the format is not recognized qlikview will consider that value as text.

     But then you need to write the logic to identify which text should be considered as date.

     Something like below.

     If(num#(Mid(DATA,5,2))>12,'Not a Valid Month',

          If(Num#(right(DATA,2)) > 31, 'Not a Valid Month')) as Flag.

     Hope the logic is clear.

Regards,

Kaushik Solanki

[iliyansomlev]

The data from the source is a string within which a date is coded in the first 8 symbols- 2013012312234. So I have to work with strings. I used the logic you mentioned, but the code becomes quite cumbersome because I have three cases in the coding logic of these strings (for dates before 1900, for XX century dates and for dates after 2000).

The idea to use IsNull as check seems short and intuitive, I will try and see how it works.

Thank you for your answers.

Best,

Iliyan

[#5]

Hi Iliyan,

please see the attached example for help.

Good luck!

Rainer

[iliyansomlev]

Thanks, this gets closest to what I wanted, it's short and fast.

[mvaugusto]

It's work for me

[Anonymous]

I tried this and it looked good but BEWARE QlikView seems to have some serious date related bugs.

It allowed me to quite happily validate 29 February 2015 and even 31 February 2015. Only when I tried 32 February it it flag it up as 'Not Valid'.

Try this in a load script

[Date Check]:
LOAD   mydate
,  ALT(DATE#( mydate ,'DD-MMM-YYYY' ),'Not valid' )  AS  [My Date]
INLINE
[mydate
28-FEB-2015
31-FEB-2015
32-FEB-2015
];

[Faizoel]

And to make things even worse. 31-feb-2015 is shown as 03-mar-2015

[eniemenm]

Makedate() function can handle whether or not the proposed date is actually a valid date. If not valid, it returns null. For example like this:

LOAD *,
makedate(mid(possible_date,1,4),mid(possible_date,5,2),mid(possible_date,7,2)) as isDate
inline [
possible_date
20150226
20150227
20150228
20150229
20150230
20150231
20150232

20160226
20160227
20160228
20160229
20160230
20160231
20160232
];

and the result looks like this: