Post reply

convert date, dateadd, and order by problem

  • February 9, 2006 at 2:58 pm

    #365073

    Hey everyone:  I have a problem properly sorting my output from the following query:

    select top 26 convert(varchar(10), DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)), 101) as WeekBeginning, count(i.ssnpn)

    from icg.dbo.mmarchive m

     left join bpcssql.dbo.ItemMasterNew_BPCS i on m.ssnpn = i.ssnpn

     left join bpcssql.dbo.as400uid_bpcs u on u.userid = m.usuid

    where m.ssvsn like 'DEL%'

    and u.Dept = 'Item Content Group'

    group by convert(varchar(10), DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)), 101)

    order by convert(varchar(10), DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)), 101) desc

    The output looks like this:

    12/26/2005
    12/19/2005
    12/12/2005
    12/05/2005
    11/28/2005
    11/21/2005
    11/14/2005
    11/07/2005
    10/31/2005
    10/24/2005
    10/17/2005
    10/10/2005
    10/03/2005
    02/06/2006
    01/30/2006
    01/23/2006
    01/16/2006
    01/09/2006
    01/02/2006

    But I would like it to have the 2006 data at the top in descending month, then day.  Date conversions are my achilles heel.

    Sorry for the format of this post, but I wanted to give as much information as possible.

    Thanks,

    JB

  • February 9, 2006 at 3:41 pm

    #619961

    I would use a derived table to calculate the required date, but keep it as a true date/time type.

    Then outside the derived table, format/sort accordingly:

     

    Select top 26 convert(varchar(10), WeekBeginning) As WeekBeginning, Count(ssnpn)

    From

    (

      select DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)) as WeekBeginning,

        i.ssnpn

      from icg.dbo.mmarchive m

        left join bpcssql.dbo.ItemMasterNew_BPCS i on m.ssnpn = i.ssnpn

        left join bpcssql.dbo.as400uid_bpcs u on u.userid = m.usuid

      where m.ssvsn like 'DEL%'

      and   u.Dept = 'Item Content Group'

    ) dt

    Group By WeekBeginning

    -- Order by true date/time type

    Order By WeekBeginning Desc

  • February 9, 2006 at 3:54 pm

    #619968

    Oct 31 200
    Oct 24 200
    Oct 17 200
    Oct 10 200
    Oct  3 200
    Nov 28 200
    Nov 21 200
    Nov 14 200
    Nov  7 200
    Jan 30 200
    Jan 23 200
    Jan 16 200

    Ummm...I think either you or I fat fingered something.  BRB.

  • February 9, 2006 at 3:56 pm

    #619972

    I fat fingered the delete key and dropped the 101 format code:

    Select top 26 convert(varchar(10), WeekBeginning, 101) As WeekBeginning

  • February 9, 2006 at 4:01 pm

    #619973

    Fantabulous.  Now I just have to re-run this thing which will take about 10 years.

    Keeping fingers crossed.

  • February 9, 2006 at 4:17 pm

    #619979

    I really appreciate the suggestion.  Alas, it is still not sorting correctly.  The 2006 data is at the bottom of the list still.  Hmmm.  Since I am a date conversion know-nothing I appreciate any further assistance.

    Justyna

  • February 9, 2006 at 4:26 pm

    #619984

    Doh. Derived table isn't generating a tru date/time datatype. Need to CAST() it:

    select CAST( DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)) As datetime)  as WeekBeginning

  • February 10, 2006 at 8:00 am

    #620144

    Select top 26 convert(varchar(11), WeekBeginning, 101) As WeekBeginning, Count(ssnpn)

    From

    (

      select CAST( DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)) As datetime)  as WeekBeginning,

        i.ssnpn

      from icg.dbo.mmarchive m

        left join bpcssql.dbo.ItemMasterNew_BPCS i on m.ssnpn = i.ssnpn

        left join bpcssql.dbo.as400uid_bpcs u on u.userid = m.usuid

      where m.ssvsn like 'DEL%'

      and   u.Dept = 'Item Content Group'

    ) dt

    Group By WeekBeginning

    Order By WeekBeginning Desc

    PW: Did I put the cast in the right place?  I think I did.  The result are still only in desc order on the month; all of the 2006 data is still at the bottom. God I need some coffee.  I wish I could buy each of you, dear readers (and especially you PW), a coffee or whiskey or whatever of your choice.  At this point I'm almost ready to promise my first born.

    Why is dealing with dates such a pain in the |_|_| ?

  • February 10, 2006 at 8:29 am

    #620157

    What is the datatype of column mmdate, what does it contain and what is the calculation trying to achieve ?

     

  • February 10, 2006 at 8:33 am

    #620163

    You know what PW, against all odds, I think I fixed it.

    Select top 26 convert(varchar(11), WeekBeginning, 101) As WeekBeginning, Count(ssnpn)

    From

    (

      select CAST( DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)) As datetime)  as WeekBeginning,

        i.ssnpn

      from icg.dbo.mmarchive m

        left join bpcssql.dbo.ItemMasterNew_BPCS i on m.ssnpn = i.ssnpn

        left join bpcssql.dbo.as400uid_bpcs u on u.userid = m.usuid

      where m.ssvsn like 'DEL%'

      and   u.Dept = 'Item Content Group'

    ) dt

    Group By WeekBeginning

    Order By datepart(yy, WeekBeginning) Desc, datepart(mm, Weekbeginning) Desc

    I added the datepart to the order by clause and it worked.

    Again, that you so much for your help.  Couldn't have done it without you!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply