topic SQL_CHAR (1) data to Date in QlikView

SQL_CHAR (1) data to Date

bnelson111 — Sat, 16 Nov 2024 01:54:10 GMT

I have data which does not respond to the following load, which would normally give me a Month.

month(Date(LastUpdatedDateTime)) as [Month Updated],

Text Displayed from LastUpdatedDateTime is 2019-11-19 15:04:19:631 + 00:00.

The above doesn't seem to respond to the override document settings format function either.

Thanks Brett

Outcome required for above is the month 11, i want to do same for Day 19 and finally year 2019 so i can create search bars.

Re: SQL_CHAR (1) data to Date

bnelson111 — Thu, 21 Nov 2019 12:19:25 GMT

I have now managed to get what appears to be a date by following

Left(LastUpdatedDateTime, 10) as DateField,

Returns 2019-11-21

i want to in the load statement get the day which is 21. Any ideas.

Year(Date#(Left(LastUpdatedDateTime, 4),'YYYY')) as [Year Updated],

this works for Year returns 2019

Re: SQL_CHAR (1) data to Date

sunny_talwar — Thu, 21 Nov 2019 13:29:10 GMT

Try this may be

Month(TimeStamp#(Trim(SubField(LastUpdatedDateTime, '+', 1)), 'YYYY-MM-DD hh:mm:ss.fff)) as [Month Updated],

Re: SQL_CHAR (1) data to Date

bnelson111 — Thu, 21 Nov 2019 13:40:56 GMT

Month(TimeStamp#(Trim(SubField(LastUpdatedDateTime, '+', 1)), 'YYYY-MM-DD hh:mm:ss.fff)) as [Month Updated],

Unfortunately no return of data on a List Box for Month Updated

Re: SQL_CHAR (1) data to Date

bnelson111 — Thu, 21 Nov 2019 13:48:43 GMT

Year(Date#(Left(LastUpdatedDateTime, 4),'YYYY')) as [Year Updated],

Above does return a usage year format?? if i could do something similaur on Day and Month from data below.

2019-11-21 09:17:44:780 + 00:00

Thanks Brett

Re: SQL_CHAR (1) data to Date

sunny_talwar — Thu, 21 Nov 2019 13:53:06 GMT

Sorry, try this

Month(TimeStamp#(Trim(SubField(LastUpdatedDateTime, '+', 1)), 'YYYY-MM-DD hh:mm:ss:fff')) as [Month Updated],

This time I tested it with your data in a text box object using the following expression

=Month(TimeStamp#(Trim(SubField('2019-11-21 09:17:44:780 + 00:00', '+', 1)), 'YYYY-MM-DD hh:mm:ss:fff'))

Re: SQL_CHAR (1) data to Date

bnelson111 — Thu, 21 Nov 2019 14:16:15 GMT

Absolutely super, in basic terms what was wrong?

Re: SQL_CHAR (1) data to Date

sunny_talwar — Thu, 21 Nov 2019 14:20:02 GMT

I think with my TimeStamp#() I was readding the micro seconds by using a .fff, whereas the format was using :fff. That changed and secondly, I missed a single quote after my format.