Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Transformation logic for date format conversion

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

9 Replies
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...

Not applicable
Author

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

Not applicable
Author

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
Master III
Master III

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
Partner - Champion III
Partner - Champion III

This is why I prefer ISO style dates like:

     YYYY-MM-DD

     YYYY/MM/DD

They are unambiguous.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
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?

Not applicable
Author

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