Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amit_saini
Honored Contributor III

Date format Help!

Hi Folks,

Below is my source date format

Tue May 24 06:43:44 BST 2016

Tue May 24 06:50:17 BST 2016

How can I convert this to 'DD-MM-YY HH;MMSmiley FrustratedS ' format ????

Thanks,

AS

1 Solution

Accepted Solutions

Re: Date format Help!

Date(Date#(Mid(DateField,5,6) & ' ' & Right(DateField,4) & ' ' & Mid(DateField,12,8),'MMM DD YYYY hh:mm:ss'),'DD-MM-YY hh:mm:ss') as YourNewDateField

or

Front End

=Date(Date#(Mid(DateField,5,6) & ' ' & Right(DateField,4) & ' ' & Mid(DateField,12,8),'MMM DD YYYY hh:mm:ss'),'DD-MM-YY hh:mm:ss')

7 Replies
sureshqv
Esteemed Contributor III

Re: Date format Help!

try

Date(date#(Daefield,'Tue May 24 06:43:44 BST 2016'), 'DD-MM-YY HH;MMSmiley FrustratedS) as newdate

migueldelval
Valued Contributor

Re: Date format Help!

Hi Amit,

If for example your date field it´s called "DATE".

date#(DATE, 'DD-MM-YY HH;MMSmiley FrustratedS ') as Date_2,

Tri it and tell me if it´s ok

Regards

Miguel del Valle

Re: Date format Help!

Hi,

try like

=date(date#(mid(Replace('Tue May 24 06:43:44 BST 2016','BST',''),5),'MMM DD hh:mm:ss  YYYY'),'MM/DD/YYYY')

Regards

Regards,
Prashant Sangle

Re: Date format Help!

Date(Date#(Mid(DateField,5,6) & ' ' & Right(DateField,4) & ' ' & Mid(DateField,12,8),'MMM DD YYYY hh:mm:ss'),'DD-MM-YY hh:mm:ss') as YourNewDateField

or

Front End

=Date(Date#(Mid(DateField,5,6) & ' ' & Right(DateField,4) & ' ' & Mid(DateField,12,8),'MMM DD YYYY hh:mm:ss'),'DD-MM-YY hh:mm:ss')

Re: Date format Help!

Hi,

update date format as per requirement.

I have set MM/DD/YYYY you can replace it with your required format DD-MM-YY HH;mmSmiley FrustratedS

Regards

Regards,
Prashant Sangle

Re: Date format Help!

Replace

Date# with TimeStamp# and

Date with TimeStamp

in my above expressions...

mikael_bjaveclo
New Contributor II

Re: Date format Help!

I'm Swedish and I would maybe have presented the timestamps in my local time, and with my local settings (

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]'; ).

This is the way of doing it.

Load *,
ConvertToLocalTime(BrittishTimeStamp, 'London', 0) As LocalTimeStamp;
LOAD *,
TimeStamp(TimeStamp#(Mid(Date, 5, 6) & ' ' & Right(Date, 4) & ' ' & Mid(Date, 12, 8), 'MMM DD YYYY hh:mm:ss'), '$(TimestampFormat)') As BrittishTimeStamp
FROM...

Community Browser