none
derive buckets from different date fields RRS feed

  • Question

  • Hi,

    I have around 4 different columns with dates in it and these columns are string/varchar data types..

    need to derive a column from these 4 fields call it as Bucket ..got stuck with converting the string as these columns has some text in it like ---------- ----- ----H which can be converted into some dummy date say 1900-01-01

    based on Today's date - get the buckets into 7 days, 14 days, 21 days, 28 days, 28Over

    look for each value in each field and derive is it 7 days from today, 14 days from today, Etc in one single column called bucket

    ---------- ----- ----H - this is what I see when I query In SQL server mgmt. studio but when I paste into excel it shown as #NAME? - any help please

    MaintenanceWindow  cycle1date MaintenanceWindow cycle3date MaintenanceWindow cycle2date MaintenanceWindow cycle4date
    #NAME? #NAME? #NAME? #NAME?
    #NAME? #NAME? #NAME? #NAME?
    2018-06-04 12:00    6H 2018-08-06 12:00    6H 2018-06-04 12:00    6H 2018-09-03 12:00    6H
    2018-06-04 12:00    6H 2018-08-06 12:00    6H 2018-06-04 12:00    6H 2018-09-03 12:00    6H
    2019-12-07 00:00    4H 2020-06-06 00:00    4H 2019-12-07 00:00    4H 2020-09-05 00:00    4H
    2020-10-10 13:00    6H 2020-12-12 13:00    6H 2020-10-10 13:00    6H 2021-01-09 13:00    6H
    2020-12-12 08:00    8H 2021-06-12 08:00    8H 2020-12-12 08:00    8H 2021-09-11 08:00    8H
    2020-10-25 04:00    6H 2020-12-27 04:00    6H 2020-10-25 04:00    6H 2021-01-24 04:00    6H
    2019-12-11 14:00    4H 2020-06-10 14:00    4H 2019-12-11 14:00    4H 2020-09-09 14:00    4H
    2019-03-06 14:00    4H 2019-09-11 14:00    4H 2019-03-06 14:00    4H 2019-12-11 14:00    4H
    2020-06-13 08:00    4H 2020-12-12 08:00    4H 2020-06-13 08:00    4H 2021-03-13 08:00    4H
    2017-10-29 08:00    6H 2017-12-24 08:00    6H 2017-10-29 08:00    6H 2018-01-28 08:00    6H
    #NAME? #NAME? #NAME? #NAME?
    #NAME? #NAME? #NAME? #NAME?
    2019-01-11 00:00    8H 2019-07-12 00:00    8H 2019-01-11 00:00    8H 2019-10-11 00:00    8H
    #NAME? #NAME? #NAME? #NAME?

    Thursday, October 22, 2020 4:00 PM

All replies


  • Hi SQLServerRaj,
    >> like ---------- ----- ----H which can be converted into some dummy date say 1900-01-01
    According to my understanding, do you mean that convert "2018-06-04 12:00    6H" to "2018-06-04"?
    If not, please explain in detail.
    >>when I query In SQL server mgmt. studio but when I paste into excel it shown as #NAME?
    Do you want to derive buckets in database or Excle?
    What is the original data of the column where “#NAME?” appears?
    And there are many reasons for #Name in Excle, such sa error appears in your formula or you miss a colon in a range reference.
    More details you can refer to this document.
    Best Regards,
    Daniel Zhang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 23, 2020 3:01 AM