topic Re: Date conversion in QlikView

Date conversion

marco_puccetti — Thu, 28 May 2015 10:47:06 GMT

Hello, i need to get the Italian Format of the date, like this should work but it doesn't work:

date#(left(publishedAt, 10), 'DD-MM-YYYY')

publishedAt is a date in timestamp form us.

Marco

Re: Date conversion

fvelascog72 — Thu, 28 May 2015 10:49:36 GMT

Hi,

Which is the format of your timestamp field?

Regards

Re: Date conversion

PrashantSangle — Thu, 28 May 2015 10:49:58 GMT

Hi,

Try below

date(date#(publishedAt,'DD-MM-YYYY hh:mm:ss tt'),'DD-MM-YYYY')

date(publishedAt,'DD-MM-YYYY')

If it not work post sample date

regards

Re: Date conversion

rubenmarin — Thu, 28 May 2015 10:53:24 GMT

Hi Marco, Date#() is used to tell QV wich is the format of the date readed, that is telling:

Transform to a Date this string wich is a date in the format 'DD-MM-YYYY'.

If you want to add another format you can enclose that expression into a Date() function:

Date(date#(left(publishedAt, 10), 'DD-MM-YYYY'), 'DD/MM/YYYY')

Re: Date conversion

Thu, 28 May 2015 11:01:37 GMT

Hi Macro,

Date#():it just evaluate the expression

Date(): formats the expression as a date

Try like this

date(date#(left(publishedAt, 10), 'DD-MM-YYYY'))

If not post format of the 'publishedAt' field..

Re: Date conversion

marco_puccetti — Thu, 28 May 2015 12:35:49 GMT

If i use this form the data is not shown!!

Marco

Re: Date conversion

Thu, 28 May 2015 12:38:48 GMT

Can you please post the format of 'publishedAt' field?

Re: Date conversion

Colin-Albert — Thu, 28 May 2015 12:39:22 GMT

Date#() sets the input format of a date

Date() sets the output format of a date

Have a look at this post Get the Dates Right

Re: Date conversion

buzzy996 — Thu, 28 May 2015 12:50:11 GMT

u have to use either of of these functions with corresponding format whichever u like to see on front end.

Date#()

Date()

Re: Date conversion

marco_puccetti — Thu, 28 May 2015 12:55:31 GMT

YYYY-mm-dd hh:mm:ss

Marco

Re: Date conversion

fvelascog72 — Thu, 28 May 2015 13:03:01 GMT

Hi,

Try this:

Date(Date#(publishedAt,'YYYY-mm-dd hh:mm:ss'),'DD-MM-YYYY')

Re: Date conversion

rubenmarin — Thu, 28 May 2015 13:05:09 GMT

Probably needs uppercase for the month and day parts:

Date(Date#(publishedAt,'YYYY-MM-DD hh:mm:ss'),'DD-MM-YYYY')

Re: Date conversion

buzzy996 — Thu, 28 May 2015 13:09:17 GMT

i think u can try this,

timestamp#(A,'YYYY-MM-DD hh_mm') where A=1997 08-

06 09_00 returns

Re: Date conversion

MarcoWedel — Thu, 28 May 2015 13:11:26 GMT

Date(Floor(Timestamp#(publishedAt,'YYYY-MM-DD hh:mm:ss')),'DD-MM-YYYY')

Re: Date conversion

Thu, 28 May 2015 13:11:26 GMT

date(date#(publishedAt,'DD-MM-YYYY') ) try this

Re: Date conversion

marco_puccetti — Thu, 28 May 2015 13:35:52 GMT

I have tried in many modes, but it still return date in yyyy-mm-dd.

This is the script:

YOUTUBE_Media:

LOAD

kind as VideoSearch_kind,

videoId as Video_id,

videoLink as VideoSearch_videoLink,

publishedAt as VideoSearch_publishedAt,

timestamp#(left(publishedAt, 10) & ' ' & mid(publishedAt, 12, 8), 'YYYY-MM-DD hh:mm:ss') as VideoSearch_publishedAt_qlik_timestamp,

date#(left(publishedAt, 10), 'DD-MM-YYYY') as VideoSearch_publishedAt_qlik_date,

channelId as VideoSearch_channelId,

channelLink as VideoSearch_channelLink,

channelTitle as VideoSearch_channelTitle,

title as VideoSearch_title,

description as VideoSearch_description,

thumbnailDefaultUrl as VideoSearch_thumbnailDefaultUrl,

liveBroadcastContent as VideoSearch_liveBroadcastContent

FROM

[$(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoSearch&appID=$(vQVSourceAppId)&query=$(site)](qvx);

Thanks

Marco

Re: Date conversion

buzzy996 — Thu, 28 May 2015 13:39:46 GMT

can u share publishedA source date as a sample?

Re: Date conversion

marco_puccetti — Thu, 28 May 2015 13:46:09 GMT

Now it's ok!!

Thanks

Marco