convert date, dateadd, and order by problem
-
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
-
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
-
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.
-
I fat fingered the delete key and dropped the 101 format code:
Select top 26 convert(varchar(10), WeekBeginning, 101) As WeekBeginning
-
Fantabulous. Now I just have to re-run this thing which will take about 10 years.
Keeping fingers crossed.
-
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
-
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
-
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 |_|_| ?
-
What is the datatype of column mmdate, what does it contain and what is the calculation trying to achieve ?
-
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply