topic Re: Datetime conversion in QlikView

Datetime conversion

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello Experts,

I want to convert timestamp from mm/dd/yyyy hh:mi:ss am/pm(4/17/2017 7:22:43 AM) format to yyyy-mm-dd hh:mm:ss format(2017-04-17 06:48:46.00).

Can anyone help me on this conversion.

Regards,

Kumar

Re: Datetime conversion

tresB — Mon, 17 Apr 2017 12:31:13 GMT

How do you get - 06:48:46.00 from 7:22:43 AM ?

Re: Datetime conversion

swuehl — Mon, 17 Apr 2017 12:39:10 GMT

In general, read your timestamp in resulting in a dual value:

Get the Dates Right

Why don’t my dates work?

If needed, use an interpretation function like TimeStamp#()

Then you can format your values like your request using formatting functions like TimeStamp()

The two format codes for your samples are

'MM/DD/YYYY hh:mm:ss TT'

resp.

'YYYY-MM-DD hh:mm:ss'

Note that minutes need a lower case 'm' and month a upper case 'M'.

Re: Datetime conversion

Anonymous — Mon, 17 Apr 2017 12:59:12 GMT

Hi Tresesco,

Thanks for your reply.

I mean that the format should be 06:48:46.00.

Can you help me how to trim trim that AM/PM part and convert to my YYYY-MM-DD hh:min:ss format

Regards,

Kumar

Re: Datetime conversion

Anonymous — Mon, 17 Apr 2017 13:06:49 GMT

Hi Stefan,

Thanks for your answers.

Actually I am trying to use these in my script. Like

Let vDate=Timestamp#(NOW(), 'YYYY-MM-DD hh:mm:ss')

and that variable value i am trying to compare database timestamp value as in the format like 2017-04-17 06:48:46.00.

But it is throwing error.

Regards,

Kumar

Re: Datetime conversion

asgardd2 — Mon, 17 Apr 2017 13:11:38 GMT

TimeStamp(Timestamp#('4/17/2017 07:22:43 AM','M/D/YYYY h:m:s TT'),'YYYY-MM-DD hh:mm:ss.00')

Re: Datetime conversion

swuehl — Mon, 17 Apr 2017 13:16:46 GMT

I assume and hope your database timestamp values are in fact numbers. You should be able to compare just numbers then (Now() also returns a number / dual value).

Re: Datetime conversion

Anonymous — Mon, 17 Apr 2017 13:19:22 GMT

Thanks a lot for your help Aleksadrov...