Date interpretation

[ashis]

Hello,

From my transaction table i am storing data in QVD first.
Now in my QVD i have few date fields and they are in
MM/DD/YYYY hh:mm:ss TT format ,

Now I changed one As_At_Date as (As_At_Date field is in 'DD/MMM/YYYY' format)
DATE(Date#(As_At_Date,'YYYY-MM-DD'),'DD-MMM-YY') AS Date, to get the desired Date field .

after these code , all the date fields are showing me in numeric value such as

Now my problem is if i want to interpret and display dates in 'DD/MM/YYYY' format with
the following code

Time(Frac(Timestamp#( Ac_Start_Time, 'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as CreatedDate
or
Timestamp#( Ac_Start_Time, 'MM/DD/YYYY hh:mm:ss TT') as CreatedTimestamp,

I am not getting any value in the list box.

Could you please help me how to display it right in 'DD/MM/YYY' format , because I need to display these field in few places.

Following is my script for your reference:

ExceptionDataFeedRun:
LOAD As_At_Date,
DATE(Date#(As_At_Date,'YYYY-MM-DD'),'DD-MMM-YY') AS Date,
Rec_Source_ID,
Start_Date,
Rec_Start_Time,
Rec_End_Time,
Rec_Status_ID,
Ac_Start_Time,
Time(Frac(Timestamp#( Ac_Start_Time, 'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as CreatedDate,
Ac_End_Time,
Ac_Status_ID,
ModifiedDate,
ModifiedUser
FROM
[..\xxx.qvd]
(qvd);

Thank you,

Ashis

[mostwanted123]

Hi Ashis,

For Date try this,

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

Regards,

Pratik

[Anil_Babu_Samineni · Accepted solution]

What are you getting when you use this simple?

Date(Start_Date) as Start_Date

[ashis]

No value I am getting.

[hari8088]

Can you share one or two samples of your date field so that it will be easy to identify.

[ashis]

Thank you Anil, it is giving me correct output.

now I can use the desire format with the following syntax

date(Ac_Start_Time,'DD/MM/YYYY hh:mm:ss') as Ac_Start_Time

Than you for your help.

[Anil_Babu_Samineni]

Great, You can mark it as correct answer to close this thread

[mostwanted123]

Your Start_Date is in timestamp format.

To get DATE from that field use,

Date(Timestamp(Timestamp#(Start_Date,'M/D/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as NewDate

Regards,

Pratik

[ashis]

Sure, I will do it , currently it is not showing in my browser.