Triplicate sorting with date conversion? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Triplicate sorting with date conversion?

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-12 : 12:45:01

Current query:

quote:


SELECT an.f_analyticID, an.f_analyticdatetime, an.f_analytictype, ISNULL(re.f_itemtype, at.f_itemtype) AS f_itemtype, ISNULL(re.f_itemname, at.f_itemname) AS f_itemname 
FROM tb_analytics an 
LEFT JOIN tb_restaurants re ON an.f_analyticassignment = re.f_itemGUID 
LEFT JOIN tb_attractionsamenities at ON an.f_analyticassignment = at.f_itemGUID 
ORDER BY f_analyticdatetime ASC, f_itemname ASC, f_itemtype ASC

"f_analyticdate" is stored in SQL as datetime, but how can I convert it to mm/dd/yyyy format in the ORDER BY clause?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:47:21

quote:

Originally posted by mattboy_slim

Current query:

quote:


SELECT an.f_analyticID, an.f_analyticdatetime, an.f_analytictype, ISNULL(re.f_itemtype, at.f_itemtype) AS f_itemtype, ISNULL(re.f_itemname, at.f_itemname) AS f_itemname 
FROM tb_analytics an 
LEFT JOIN tb_restaurants re ON an.f_analyticassignment = re.f_itemGUID 
LEFT JOIN tb_attractionsamenities at ON an.f_analyticassignment = at.f_itemGUID 
ORDER BY f_analyticdatetime ASC, f_itemname ASC, f_itemtype ASC

"f_analyticdate" is stored in SQL as datetime, but how can I convert it to mm/dd/yyyy format in the ORDER BY clause?

you dont need to
provided its datetime it will be sorted correctly based on date
you need to be worried about format only while displaying dates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-12 : 14:04:30

Actually, in my case, I need to sort by mm/dd/yyyy. Here is why:

Current Query:

quote:


SELECT an.f_analyticID, an.f_analyticdatetime, an.f_analytictype, ISNULL(re.f_itemname, at.f_itemname) AS f_itemname 
FROM tb_analytics an 
LEFT JOIN tb_restaurants re ON an.f_analyticassignment = re.f_itemGUID 
LEFT JOIN tb_attractionsamenities at ON an.f_analyticassignment = at.f_itemGUID 
ORDER BY f_analyticdatetime ASC, f_itemname ASC, f_analytictype ASC

My current query bring the results like this:

quote:


f_analyticGUID	f_analyticdatetime	f_analytictype	f_itemname
145		2010-01-04 12:01:35.000	printcoupon	Boston Market
130		2010-02-08 11:53:35.000	view		Boston Market
131		2010-02-08 11:55:52.000	printmap	Boston Market
140		2010-02-08 11:56:41.000	printcoupon	Boston Market
139		2010-02-08 11:56:41.000	view		Boston Market
133		2010-02-09 11:55:55.000	printcoupon	Boston Market
132		2010-02-09 11:55:55.000	view		Boston Market
134		2010-02-09 11:55:59.000	view		Boston Market
135		2010-02-09 11:56:36.000	view		Boston Market

But I need it to be in this order so that the types are in order. Notice in the first query the time is preventing the proper ordering

quote:


f_analyticGUID	f_analyticdatetime	f_analytictype	f_itemname
145		2010-01-04 		printcoupon	Boston Market
140		2010-02-08		printcoupon	Boston Market
131		2010-02-08		printmap	Boston Market
130		2010-02-08 		view		Boston Market
139		2010-02-08		view		Boston Market
133		2010-02-09		printcoupon	Boston Market
133		2010-02-09		printmap	Boston Market
132		2010-02-09		view		Boston Market
134		2010-02-09		view		Boston Market
135		2010-02-09		view		Boston Market

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-12 : 15:54:20

I got it figured out. If I convert it in the select statement, I get the proper results:

Here is the correct query:

quote:


SELECT an.f_analyticID, convert(varchar, an.f_analyticdatetime, 1) AS f_analyticdatetime, an.f_analytictype, ISNULL(re.f_itemname, at.f_itemname) AS f_itemname 
FROM tb_analytics an 
LEFT JOIN tb_restaurants re ON an.f_analyticassignment = re.f_itemGUID 
LEFT JOIN tb_attractionsamenities at ON an.f_analyticassignment = at.f_itemGUID 
ORDER BY f_analyticdatetime ASC, f_itemname ASC, f_analytictype ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 10:17:38

quote:

Originally posted by mattboy_slim

I got it figured out. If I convert it in the select statement, I get the proper results:

Here is the correct query:

quote:


SELECT an.f_analyticID, an.f_analyticdatetime, an.f_analytictype, ISNULL(re.f_itemname, at.f_itemname) AS f_itemname 
FROM tb_analytics an 
LEFT JOIN tb_restaurants re ON an.f_analyticassignment = re.f_itemGUID 
LEFT JOIN tb_attractionsamenities at ON an.f_analyticassignment = at.f_itemGUID 
ORDER BY f_analyticdatetime ASCdateadd(dd,datediff(dd,0,f_analyticdatetime), 0), f_itemname ASC, f_analytictype ASC

Nope you dont need to change the datatype for sorting, you just need to do like above

see result below



f_analyticGUID	f_analyticdatetime	f_analytictype	f_itemname
145	2010-01-04 12:01:35.000	printcoupon	Boston Market
140	2010-02-08 11:56:41.000	printcoupon	Boston Market
131	2010-02-08 11:55:52.000	printmap	Boston Market
130	2010-02-08 11:53:35.000	view	Boston Market
139	2010-02-08 11:56:41.000	view	Boston Market
133	2010-02-09 11:55:55.000	printcoupon	Boston Market
134	2010-02-09 11:55:59.000	view	Boston Market
135	2010-02-09 11:56:36.000	view	Boston Market
132	2010-02-09 11:55:55.000	view	Boston Market

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources