Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
dushan1987
New Contributor II

Date format issue

Hi, 

When I change the julian date into calender date, It is getting the date format as below

M/D/YYYY 12:00:00 AM

I want to change this into YYYY/MM/DD format. 

but Date#(M/D/YYYY 12:00:00 AM,'YYYY/MM/DD') is not working. I am getting data from AS400 database.

I am using following query to make the julian to calender dateconversion.

Date#(TimeStamp ( MakeDate( Floor(GBUPDT / 1000) ) + 1000 * Frac(GBUPDT / 1000) -1 ),'YYYY/MM/DD')as GLC_Date

Please help on this

Thanks in advance

Tags (1)
1 Solution

Accepted Solutions

Re: Date format issue

Julian format means - How much byte it got stored? May be just this?

Date(Date#(GBUPDT, 'M/D/YYYY hh:mm:ss TT'),'YYYY/MM/DD')

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
7 Replies
Partner
Partner

Re: Date format issue

Hi!

1) You triyng interpet timestamp by datestamp format. Format parameter in Date# is for input string interpretation as date, Date without # is for integer Date value format conversion.

2) What is M/D/YYYY 12:00:00 AM  in Date#(M/D/YYYY 12:00:00 AM,'YYYY/MM/DD') expression?

Its must be a string or field/variable with text date representation.

Use it like this: Date#('12/20/2018','MM/DD/YYYY')

Re: Date format issue

Julian format means - How much byte it got stored? May be just this?

Date(Date#(GBUPDT, 'M/D/YYYY hh:mm:ss TT'),'YYYY/MM/DD')

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
dushan1987
New Contributor II

Re: Date format issue

HI, 

M/D/YYYY 12:00:00 AM is the date format which is coming from the julian to calender conversion.

Re: Date format issue

Can you post few records from GBUPDT with expected result

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
dushan1987
New Contributor II

Re: Date format issue

Thanks 

dushan1987
New Contributor II

Re: Date format issue

Hi, resolved the previous issue. Also I need to get the monthly average when selecting a date. when I select a date, the value should not be changed by date and should remain as the monthly average. Please help on this too

Re: Date format issue

Please open new thread from next on wards, Perhaps this?

Avg({<Date=>} TOTAL <Month> Sales)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)