Date format issue

[dushan1987]

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

[Zhandos_Shotan]

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')

[Anil_Babu_Samineni · Accepted solution]

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')

[dushan1987]

HI, 

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

[Anil_Babu_Samineni]

Can you post few records from GBUPDT with expected result

[dushan1987]

Thanks 

[dushan1987]

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

[Anil_Babu_Samineni]

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

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