Date format

[srinivasa1]

Hi can any once guide how I can make datekey from below date.

Date

6/23/2014 1:59:56 AM

10/14/2014 3:18:05 PM

10/14/2014 4:05:29 PM

10/14/2014 4:11:25 PM

10/14/2014 4:13:16 PM

10/14/2014 4:23:43 PM

I wanted in below datekey format

20140623

20141014

Thanks for any help.

[swuehl · Accepted solution]

Try a combination of Timestamp#() interpretation, Date() formatting and Num#() interpretation:

=Num#(Date(Timestamp#('6/23/2014 1:59:56 AM','M/D/YYYY h:m:s TT'),'YYYYMMDD'))

LOAD

     Date,

     Num#(Date(Timestamp#(Date,'M/D/YYYY h:m:s TT'),'YYYYMMDD')) as DateKey,

     ...

[sunny_talwar]

You can also use try this if the date key represents date rather than a number:

Date(Date#(SubField(Date, ' ', 1), 'M/D/YYYY'), 'YYYYMMDD') as DateKey

[el_aprendiz111]

HI

2-options:

1)   DATE([Date],'YYYYMMDD') AS datekey

2)    DATE(Floor([DueDate]),'YYYYMMDD') AS datekey

[srinivasa1]

Wow swuehl. Perfect!!!.

Thanks@

[srinivasa1]

Thanks Sunny T. your also working!.

[Digvijay_Singh]

Hi Stefan,

Will this calculated DateKey contain decimal part as well? Wouldn't it create problem in association, I was thinking if we should use floor to remove decimal part from number representation in case we use this field for association.

Thanks,

DJ

[trdandamudi]

You can also try the below:

     Date(Floor(Date),'YYYYMMDD') as DateKey

[swuehl]

No, the decimal part is stripped off by using Num#() interpretation function, interpreting a six digit integer number format text value like 20140623. Num# will set the numeric representation of your dual accordingly.

That's the whole purpose I am using this function.

[Digvijay_Singh]

Oh yeah, missed the point that Num#() will take text representation of the calculated value before its application.

Thanks for clarifying!

Regards,