Select as VarChar but order by DateTime

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)

Select as VarChar but order by DateTime

Author

Topic

jemajoign
Starting Member

7 Posts

Posted - 2008-03-13 : 12:14:57

Hello,

I'm trying to CONVERT some DateTime column to VarChar in the SELECT part but still do a DateTime ORDER BY while using a UNION.

In other words, does anyone know how to make this work:


create table #temp1 (
	d datetime
)

create table #temp2 (
	d datetime
)

insert into #temp1(d)
select '2001-12-12' union
select '2002-11-11'

insert into #temp2(d)
select '2003-10-10' union
select '2004-09-09'


--works fine-------------------------------
select convert(varchar, d, 101) dd from #temp1
order by d
-------------------------------------------



--but can't make this work-------------------

select convert(varchar, d, 101) dd from #temp1
union select convert(varchar, d, 101) dd from #temp2

order by dd                     -- wrong type of sorting

--order by d			-- error

--order by cast(dd as datetime) -- error

-------------------------------------------

drop table #temp1
drop table #temp2

I would really appreciate your help.

Thanks in advance,
Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-13 : 12:25:36

may be this

select d,convert(varchar, d, 101) dd from #temp1
union select d,convert(varchar, d, 101) dd from #temp2
order by d

why are you trying to convert it to varchar in first place. If its for presentation, try to do it at your front end.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-13 : 12:29:21

[code]
select
convert(varchar,a.dd,101) dd
from
(
select dateadd(dd,datediff(dd,0,d),0) as dd from #temp1
union
select dateadd(dd,datediff(dd,0,d),0) as dd from #temp2
) a
order by
a.dd

[/code]

CODO ERGO SUM

jemajoign
Starting Member

7 Posts

Posted - 2008-03-13 : 12:50:14

Thanks for your responses. Your solution works great Michael. I don't know why I didn't think of that. I've implemented it.

Visakh: Theoretically (in terms of data vs presentation), you're probably right about doing this on the front-end, but I find it easier in practice to take care of whatever possible in the query itself. This is mostly because I've built a lot of abstractions/tools for directly outputting query results in different ways. So in order to use those tools, I need queries that don't require any modification before presentation.

Thanks again

jemajoign
Starting Member

7 Posts

Posted - 2008-03-13 : 12:52:46

OK I used the idea from your original response. Why did you edit it as such? Is this some sort of strange performance thing?

How is that different than:

select
convert(varchar,a.d,101) dd
from
(
select d from #temp1
union
select d from #temp2
) a
order by
a.d

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 13:34:03

quote:

This is mostly because I've built a lot of abstractions/tools for directly outputting query results in different ways. So in order to use those tools, I need queries that don't require any modification before presentation.

It is much easier to let your tools accept raw, properly typed data and let them formatting and producing output, rather than always returning nothing but VARCHARs from your SQL statements and forcing your tools to only output meaningless strings in only one format. Your set up means your sql is slower, harder to maintain, and more complicated and your front-end tools are less powerful, less flexible, and less useful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources