Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sort by date not string

Author  Topic 

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-02-16 : 08:16:35
In my table date is set up as Char.

How do I sort it by date as it is being sorted by string when I do order by filedate.

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2012-02-16 : 08:28:03
ORDER BY CAST(Col1 AS DATE)


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2012-02-16 : 08:28:41
You need to convert the char to date. (EDIT) Peso is too fast for me today!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-02-16 : 08:37:20
Thanks I had to add datetime to it because I got this error with just date:

I'm getting Type date is not a defined system type



Order by cast(filedate as datetime)

Go to Top of Page

rajan.nithin
Starting Member

42 Posts
Posted - 2012-02-16 : 08:39:34
In what format is the date stored ?
is it like 2012/02/03?

quote:
Originally posted by JJ297

In my table date is set up as Char.

How do I sort it by date as it is being sorted by string when I do order by filedate.
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-02-16 : 08:43:28
It's stored as 11/02/2009
Go to Top of Page

rajan.nithin
Starting Member

42 Posts
Posted - 2012-02-16 : 08:53:24
You could also try:
ORDER BY CONVERT(date,filedate)

quote:
Originally posted by JJ297

It's stored as 11/02/2009
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-02-16 : 09:04:41
Thanks that worked but I failed to tell you I have another column that needs to have the oldest to the earliest date sorted on as well.

How do I add another field so when I click on that field next it sorts. The field is called ddsreceipt

ORDER BY CONVERT(datetime,filedate)

How would I add it here?
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-02-16 : 09:06:00
Here's the entire query:


Select p.DOC,
		p.Reg,  
n.RegionAcronym,
		FO,
	  CLMSSN,

Age = Datediff(day,max(filedate), getdate()),
FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(ddsrcpt), getdate()),
	Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From 
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum 
from pendingdds) p
join natdocfile n on n.reg = p.reg
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc in ('S0B')
Group By p.filedate, FO, p.Reg,n.regionacronym,  p.DOC, CLMSSN, o.ddsofficecode,
o.mailingaddressstate
ORDER BY CONVERT(datetime,filedate)
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2012-02-16 : 09:16:00
Step 1, scream at the DBO until the fields are changed to DATETIME fields. Step 2, order by those fields.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-02-16 : 09:18:36
Okay thanks. Does my group by have anything to do with the order by? Do I need to place the group by in a particular order?
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 09:53:17
quote:
Originally posted by DonAtWork

Step 1, scream at the DBO until the fields are changed to DATETIME fields.


datetime2

But in any case, they are not using SQL Server 2008

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-02-16 : 11:04:19
I'm sorry I don't understand what you mean by datetime2



When I come into the page the first time I want to sort by age first with the oldest age at the top.



FO    CLMS     AGE   FileDate   Code     DDSRecedipt
278   125698   570   07/26/2010 S23      09/08/2011
195   569874   524   09/10/2010  S23     01/06/2012
278   659874   444   07/26/2010  S23     11/07/2011



when I click on FileDate I want it to sort like this:



FO    CLMS     AGE   FileDate   Code     DDSReceipt
278   125698   570   07/26/2010 S23      09/08/2011
278   659874   444   07/26/2010  S23     11/07/2011
195   569874   524   09/10/2010  S23     01/06/2012



when I click on DDSReceipt I want it to sort like this:

FO    CLMS     AGE   FileDate   Code     DDSReceipt
278   125698   570   07/26/2010 S23      09/08/2011
278   659874   444   07/26/2010  S23     11/07/2011
195   569874   524   09/10/2010  S23     01/06/2012



How would I write the group by and order by for this query:


Select p.DOC,
		p.Reg,  
n.RegionAcronym,
		FO,
	  CLMSSN,

Age = Datediff(day,max(filedate), getdate()),
FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(ddsrcpt), getdate()),
	Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From 
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum 
from pendingdds) p
join natdocfile n on n.reg = p.reg
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc in ('S0B')
Group By p.filedate, FO, p.Reg,n.regionacronym,  p.DOC, CLMSSN, o.ddsofficecode,
o.mailingaddressstate
ORDER BY CONVERT(datetime,filedate)
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 11:42:18
What Version of SQL Server are you using?

Why do you need a GROUP BY...you are not Doing any Scalar Functions...and what do you mean by Click on?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-02-16 : 11:50:49
when I don't add group by and run the stored procedure I get this error message:

Column 'p.doc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


So I added it:

Select p.DOC,
		p.Reg,  
n.RegionAcronym,
		FO,
	  CLMSSN,

Age = Datediff(day,max(filedate), getdate()),
FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(ddsrcpt), getdate()),
	Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From 
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum 
from pendingdds) p
join natdocfile n on n.reg = p.reg
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc in ('S0B')
Group By p.filedate, p.ddsrcpt, p.doc , p.reg, n.regionacronym, p.fo, p.clmssn, MailingAddressState, o.ddsofficecode
order by doc, age desc
ORDER BY age desc, filedate, ddsreceipt




What I mean by click on is this stored procedure is used in a Gridview with sortable columns. So when I sort on those columns I want them to be in desc order. Do I do that in .net?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2012-02-17 : 03:40:23
ORDER BY CONVERT(datetime, filedate, 101)


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2012-02-17 : 07:57:37
Click on = front end. Your datagrid should have a column header event you can handle, and most likely a sort property.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-02-17 : 13:12:36
Thanks for the replies I changed the field to Datetime which was much easier to handle!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site