Forum Discussion

Nandor's avatar
Nandor
Helper I
9 years ago
Solved

Generate a date key

Hi,

 

I have a classical Date&Time column in my database.

I would need to add a new column generated from year, month and day.

 

For example: 20160905 for the 5th of september 2016.

 

Thank you,

Nandor

Need Help
  • tjd's avatar
    tjd
    9 years ago

    Nandor -

     

    Try this: DateInt = ([Year]*10000)+([Month Nbr]*100)+[DayOfMonth])

  • Framet's avatar
    Framet
    9 years ago

    Hi,

     

    Some very valid suggestions, my personal choice would be to have the following calcualted column:

    =FORMAT ( [YourDateField], "YYYYMMDD" )

     

    Cheers

5 Replies

  • grapefruitmoon's avatar
    grapefruitmoon
    Advocate I
    9 years ago

    In Power Query/M you could use:

     

    Date.ToText([DateColumn], "yyyMMdd")

    • Nandor's avatar
      Nandor
      Helper I
      9 years ago

      Thank you!

       

      All three solutions work perfectly.

       

      Best regards,

      N

  • Framet's avatar
    Framet
    Resolver II
    9 years ago

    Hi,

     

    Some very valid suggestions, my personal choice would be to have the following calcualted column:

    =FORMAT ( [YourDateField], "YYYYMMDD" )

     

    Cheers

  • tjd's avatar
    tjd
    Impactful Individual
    9 years ago

    Nandor -

     

    Try this: DateInt = ([Year]*10000)+([Month Nbr]*100)+[DayOfMonth])

  • cabc_xlorla's avatar
    cabc_xlorla
    Advocate IV
    6 years ago

    All excellent solutions!

    1. Date.ToText([DateColumn], "yyyMMdd")

    2. DateInt = ([Year]*10000)+([Month Nbr]*100)+[DayOfMonth])
    3. =FORMAT ( [YourDateField], "YYYYMMDD" )

     

    thanks everyone!