date# format string for ISO date with extra characters

[stevedark]

Hi there,

I am importing some log files, and they have a date in there with the ISO format, which has both a T and a Z included in the string as well as the date information.

Could anyone please tell me the date format string I should use with date# to pull this in.

Obviously I could do a replace on the string first to remove the extra characters, or use substrings to pull out parts of the string - but I assume there must be a way of doing it natively with date#.

Any pointers gratefully received.

Cheers,
Steve

[disqr_rm]

Can you provide a sample string for the date you have in the file?

[Anonymous]

Hi Steve,

Is this the format you're refering to: YYYY-MM-DDThh:mm:ssZ

It seems to be the T that causes confusion for QV. I'm not sure if capital T is used for any part of a date evaluation. If I change the T to X for example it works fine.

=date(date#('1994-11-05X13:15:30Z', 'YYYY-MM-DDXhh:mm:ssZ'))

[stevedark]

Hi Jsn / Rakesh,

The format is indeed YYYY-MM-DDThh:mm:ss.fffZ, eg. 2009-04-29T17:26:09.232Z.

I have gotten around the issue by using this code:

date(date#(replace(replace([Timestamp], 'T', ' '), 'Z', ''), 'YYYY-MM-DD hh:mm:ss.fff'))

I was surprised that the date# function returned null if the date being converted had an extra character on the end to the mask. Simply replacing the T with a space wasn't enough, I had to replace the Z with an empty string also.

I still feel there must be a cleaner way of doing this though, without the extra string manipulation.

Cheers,
Steve

[disqr_rm · Accepted solution]

Hi Steve, still not the best way, but atleast it will get rid of one "replace"

date(date#(purgechar('2009-04-29T17:26:09.232Z', 'TZ'), 'YYYY-MM-DDhh:mm:ss.fff'))

[rwunderlich]

Steve,

I've always purged the "T". "T" has meaning as an AM/PM mask character.

But....you only seem to be wanting the date, not the time, so a simpler approach might be:

date(date#(left(Timestamp, 8), 'YYYY-MM-DD')

-Rob

[stevedark]

Hi there,

It would appear that there is no route that is as elegant as in my native tounge...

CONVERT(DATETIME, '2009-04-29T17:26:09.232Z', 127)

But at least it is not too much of a drag to do the string manipulation. Thanks for the heads up on purgechar - I can see that being useful for other purposes also.

Cheers,
Steve

[#8]

Hi Rob

Thanks for the function. My application was floudering yesterday when I discovered there were two seperate entries for the same date as there was a timestamp attached at the end. I think that this is one of the most elegant ways to removing unwanted data. I took the first 10 digits instead of 8 digits and it works fine.

[JuanManuelLopezFelizzola]

Well I am sure Steve will have a little laugh with a response to a post from 10 years ago, but was just going to log this here since it is related to this.

Ok, so our DB is a MongoDB hosted on AWS, and we have some ODBC for connecting to that, provided by CData.

Connection goes well and it brings the data, but I have discovered that the ODBC is bringing a bunch of metadata from the DB and it makes the QVD (insanely) slow to save and to concatenate several QVD with the same structure. Also, some of the dates that are stored in ISODate in the DB do not come forward when pulled and modified a bit in the EXT.

What I had to do, was create a dummy TXT file in which I would save the extracted data without any modification done, then drop the table, and reload from the TXT again, then the information would be stored as text in there. After that is just a matter of fine tunning the format for the whole date and time to be recognized. For that, I ended up using something like (when loading from the TXT of course)

Date(Floor(Date#(Date_Main_TMP, 'DD/MM/YYYY hh:mm:ss tt'))) as Date_Main

(lol... the insert code highlighting doesn't have option for QlikView script)