Transformation logic for date format conversion

[#1]

Hi

Is there a function to verify a date field which is present in dd/mm/yyyy hh:mm:ss which is getting transformed mm/dd/yy hh:mm:ss.

[sunny_talwar]

For a date which looks like this

03/03/2017 or 04/03/2017.... it would be very difficult to know, but you can convert this into a number to see which date format is it actually formatting into...

[#3]

But when it is converted to a number, we can get the same number irrespective of any format. That would be pretty difficult to compare the Date format conversion.

[sunny_talwar]

04/03/2017 as MM/DD/YYYY will give you a different number then 04/03/2017 as DD/MM/YYYY

[#5]

For the same date lets say 03-Apr-2017 if it is going to be dd/mm/yyyy -03/04/2017 and mm/dd/yyyy -04/03/2017 the value remains same. How can we verify this scenario the date is getting converted to this format.

[sasiparupudi1]

Check the setting of the following variable in your qvw which dictates the default format for the model

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

hth

Sas

[jonathandienst]

This is why I prefer ISO style dates like:

     YYYY-MM-DD

     YYYY/MM/DD

They are unambiguous.

[sunny_talwar]

I think I am not completely sure what you have and what you are looking to get.... Would you be able to share a sample and elaborate on your issue a little more?

[#9 · Accepted solution]

I actually have two date fields like below with a date field in data base . I have a transformation logic for which we need to converted to another format i.e From the format of date F1 to date F2.

F1 - 8/25/2012 12:00:01 AM

F2  - 25/8/2012 12:00:01 AM

I have written the below code and it works fine for me now. Thank you all.

t1:

LOAD * INLINE [

    F1, F2

    8/25/2012 12:00:01 AM, 25/8/2012 12:00:01 AM

];

store * from t1 into t1_inline.qvd(qvd);

drop Table t1;

t2:

LOAD F1,

     F2,if(Timestamp(Timestamp#(F1,'MM/DD/YYYY hh:mm:ss TT'),'DD/M/YYYY hh:mm:ss TT')=F2,'Y','N'),Timestamp(Timestamp#(F1,'MM/DD/YYYY hh:mm:ss TT'),'DD/M/YYYY hh:mm:ss TT') as converted_data

FROM

t1_inline.qvd(qvd);

[sunny_talwar]

You can simplify this, using this

LOAD F1,

     F2,

     If(TimeStamp#(F1,'MM/DD/YYYY hh:mm:ss TT') = F2,'Y','N') as Flag,

     TimeStamp(TimeStamp#(F1,'MM/DD/YYYY hh:mm:ss TT'),'DD/M/YYYY hh:mm:ss TT') as converted_data

FROM

t1_inline.qvd(qvd);