Order converted dates in union query

I have the following as part of a union query:

CONVERT(CHAR(8), r.RRDate, 1) AS [Date]

I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.

Thanks,

Dave

Do you really require UNION operator? If the results of each SELECT statement in the UNION is distinct then use UNION ALL. This will also provide better performance since it doesn't do the duplicate elimination step. And if you use UNION ALL then you can use the column name "r.RRDate" in the ORDER BY clause. If you need to use UNION then only way is to specify the column in the SELECT list also if you want it in the ORDER BY clause. Lastly, is there any reason for your to format the date in the query itself. It is usually unnecessary work to do this on the server-side. It is best to send the date value as is and format on the client. Alternatively, you can use a style which is universal and will preserve sorting for example like the ISO unseparated date format (style 112: YYYYMMDD) or ISO 8601 datetime format (style 126: YYYY-MM-DDThh:mm:ss.nnn). Using language dependent style format is always confusing and can cause errors when you try to use it as is in a different system that has a different language setting for example.

ORDER BY problem with CONVERT

Hi,
I just realized that when I started using the CONVERT function on my dates in my SELECT statement and try to ORDER BY one of the date fields that I convert, the order isn't actually correct. Here's the statement:

$query = "SELECT id, BroSisFirstName, BroSisLastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, BroSisFirstNameLP, BroSisLastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),101) AS checkedOutDate, CONVERT(char(10),CONVERT(datetime, CAST(returnedDate as varchar(12))),101) AS returnedDate, CONVERT(char(10),CONVERT(datetime, CAST(lpcheckedOutDate as varchar(12))),101) AS lpcheckedOutDate, CONVERT(char(10),CONVERT(datetime, CAST(lpReturnedDate as varchar(12))),101) AS lpReturnedDate FROM Checkouts WHERE IsClosed < 1 ORDER BY checkedOutDate";

It's almost as if it's treating the date as a string. Does anybody know why, and how I can correct the issue? I need to use the CONVERT function because I don't want the whole 00:00:00 returned with each date. And I say it's the CONVERT function because if I take off the CONVERT on one of the fields such as checkedOutDate and try to sort by it, it sorts correctly.try changing your sql so that your select convert(...) as xyz uses different names...

eg. if you are converting checked_out_date don't select it as checked_out_date, try selecting it as checked_out_date1.

Alternatively include the order by date as another field in your query and don't convert it. You will need to select it as something else check order_checked_out_date and then sort on that field.

I don't know if it will work, but it's worth a try.|||Well, I tried your first idea, but it didn't work, but don't quite understand your second idea - I'm new to SQL so I was wondering if you could elaborate your explanation? Thanks|||does this help??

$query = "SELECT id, BroSisFirstName, BroSisLastName, TerrNumber,
IsCheckedOut, IsLPCheckedOut, BroSisFirstNameLP, .... etc .... CAST(lpReturnedDate as varchar(12))),101) AS lpReturnedDate, checkedOutDate as order_date FROM Checkouts WHERE IsClosed < 1 ORDER BY order_date";|||That did it! Thanks! Sorry for being so lame - like I said before, I'm just a newbie.|||no worries, it's not always easy to figure out what other people mean when you are swapping emails etc...

I'm not sure if that is the best way to do things, but I'm glad it worked. :)|||I'm surprised his original query did not sort correctly, even if he did use the same name as an alias. :confused:

I wonder if it would have worked if he had just fully qualified the field in the SORT statement:

"ORDER BY Checkouts.checkedOutDate"|||possible.... not sure to be honest... it kinda surprised me as well but then (no offense meant) it is a MS product and their behaviour can be a bit perverse. ;)|||I wonder if it has something to do with the interface he is using? It doesn't look like he's executing through query analyzer or a stored proc. Perhaps something is doing some independent interpretation of his code before it is sent to the server?

Hmmm...|||Well, what you said went over my head blindman, but if it helps, I'm just using PHP on Windows XP Pro w/Apache web server, and I'm executing my query through my PHP scripts.|||Now you are over my head.

It might be worth checking Current Activity Process Info in Enterprise Manager to see exactly what statement is being sent to SQL server.

Whatever works, I guess!|||Ok, I'll look for that and check it out - thanks.sql

ORDER BY earliest date in a row

Hi,

I have a table (SQL Server 2000) with several date columns in it, all of
which are individually NULLable, but in any one row, not all the dates can
be NULL.

I want a query which ORDERs BY the earliest date it finds in each row. I'm
guessing I have to do this in two steps:

STEP 1
Using a UDF, find the earliest date and stick it in a new calculated
column "earliest date"

STEP 2
ORDER BY this UDF-created column

If this is the right way to go about this, is there a simple SQL way of
determining which is the lowest of several dates? (ie of doing STEP 1).

Or am I looking at this the wrong way, and missing an easy *one-step* way of
getting what I want?

TIA,

JON"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bk7kdg$gva$1@.online.de...
> Hi,
> I have a table (SQL Server 2000) with several date columns in it, all of
> which are individually NULLable, but in any one row, not all the dates can
> be NULL.
> I want a query which ORDERs BY the earliest date it finds in each row.
I'm
> guessing I have to do this in two steps:
> STEP 1
> Using a UDF, find the earliest date and stick it in a new
calculated
> column "earliest date"
> STEP 2
> ORDER BY this UDF-created column
> If this is the right way to go about this, is there a simple SQL way of
> determining which is the lowest of several dates? (ie of doing STEP 1).
> Or am I looking at this the wrong way, and missing an easy *one-step* way
of
> getting what I want?
> TIA,
> JON

This is probably easier to do in a client/reporting tool than in pure SQL,
but one possible solution is as follows (performance won't be good on a
large table):

create view dbo.DateCols
as
select KeyCol, DateCol1 as 'DateCol'
from dbo.MyTable
union
select KeyCol, DateCol2
from dbo.MyTable
union
select KeyCol, DateCol3
from dbo.MyTable

select t.*
from dbo.MyTable t
join
(
KeyCol, min(DateCol) as 'MinDate'
from dbo.MyTable
group by KeyCol
) as dt
on t.KeyCol = dt.KeyCol
order by dt.MinDate

Simon|||[sent to microsoft.public.sqlserver.programming separately - newsreader can't
sent to 2 news servers at once.]

Jon,

Here is another option, using Alejandro's definitions (thanks, Alejandro!)

create view vwTable1A
as
select table1.c1,
case n when 2 then c2 when 3 then c3 when 4 then c4 end c,
case n when 2 then '2' when 3 then '3' when 4 then '4' end i
from table1, (
select 2 n union all select 3 union all select 4
) N
go

select
c1,
(select c from vwtable1A where c1 = T.c1 and i = '2') c2,
(select c from vwtable1A where c1 = T.c1 and i = '3') c3,
(select c from vwtable1A where c1 = T.c1 and i = '4') c4
from vwtable1A T
group by c1 order by min(c)

-- Steve Kass
-- Drew University
-- Ref: 044B6F84-937C-4CDE-B9F0-BBEB959DBB7F

Jon Maz wrote:

>Hi,
>I have a table (SQL Server 2000) with several date columns in it, all of
>which are individually NULLable, but in any one row, not all the dates can
>be NULL.
>I want a query which ORDERs BY the earliest date it finds in each row. I'm
>guessing I have to do this in two steps:
> STEP 1
> Using a UDF, find the earliest date and stick it in a new calculated
>column "earliest date"
> STEP 2
> ORDER BY this UDF-created column
>If this is the right way to go about this, is there a simple SQL way of
>determining which is the lowest of several dates? (ie of doing STEP 1).
>Or am I looking at this the wrong way, and missing an easy *one-step* way of
>getting what I want?
>TIA,
>JON
>|||Jon,

I would try this (Air coded):

SELECT MT.*
FROM MyTable MT
INNER JOIN(
SELECT T.RecordID, MIN(T.MinDate) AS MinDate
FROM
(SELECT RecordID, Date1 AS MinDate
FROM MyTable
UNION ALL
SELECT RecordID, Date2 AS MinDate
FROM MyTable
UNION ALL
SELECT RecordID, Date3 AS MinDate
FROM MyTable
UNION ALL
... /* Any other date field in your record ... */
) AS T
GROUP BY T.RecordID) AS T ON T.RecordID = MT.RecordID
ORDER BY T.MinDate

HTH

Yannick

"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bk7kdg$gva$1@.online.de...
> Hi,
> I have a table (SQL Server 2000) with several date columns in it, all of
> which are individually NULLable, but in any one row, not all the dates can
> be NULL.
> I want a query which ORDERs BY the earliest date it finds in each row.
I'm
> guessing I have to do this in two steps:
> STEP 1
> Using a UDF, find the earliest date and stick it in a new
calculated
> column "earliest date"
> STEP 2
> ORDER BY this UDF-created column
> If this is the right way to go about this, is there a simple SQL way of
> determining which is the lowest of several dates? (ie of doing STEP 1).
> Or am I looking at this the wrong way, and missing an easy *one-step* way
of
> getting what I want?
> TIA,
> JON
>|||Another one (using Alejandro's DDL):

SELECT *
FROM Table1
ORDER BY
(SELECT MIN(dt)
FROM
(SELECT c2 AS dt
UNION ALL
SELECT c3
UNION ALL
SELECT c4) AS d)

--
David Portas
----
Please reply only to the newsgroup
--|||Hi,

Thanks to all for the great replies.

I ended up using a variant on David's code (because it was the shortest) and
using it in a UDF. A cut-down version of this UDF is below, and I was just
wondering if there a more succint way of writing it (ie without all the
repetition of 'WHERE CaseID=@.CaseID'?)

Cheers,

JON

__________________________________________________ __

CREATE FUNCTION EarliestDate(@.CaseID as INT, @.DateType as VarChar(255))
RETURNS DateTime
AS
BEGIN

DECLARE @.RESULT DateTime
SET @.RESULT = ''

IF @.DateType = 'NonWECLetters_SentToClient'

SELECT @.RESULT =

MIN(dt)
FROM
(SELECT DateBWSLettSentClient AS dt FROM tblCases WHERE CaseID=@.CaseID
UNION ALL
SELECT DateRMLLettSentClient FROM tblCases WHERE CaseID=@.CaseID
UNION ALL
SELECT DateBWSLettEqChSentClient FROM tblCases WHERE CaseID=@.CaseID) AS
d

RETURN @.RESULT

END
GO

Order By Date Format

I have inherited some code with a view that formats dates like
Mar 26, 2003
May 06, 2006
Jun 05, 2006
Example
SELECT DISTINCT TOP 5000 createDate, responsibilities
FROM vw_someview
ORDER BY createDate DESC,responsibilities
the order by doesnt come out right. The view select for the field is
convert( varchar(50), pp.createDate, 107 ) as createDate,
It is stored as 2003-03-26 14:29:41.880
Id like to order by properly but was wondering if I can do another
convert to make it order by properly?
Thanksuse order by convert(datetime,CreateDate)
take a look at these 2 example
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by CreateDate
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by convert(datetime,CreateDate)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Brian wrote:
> I have inherited some code with a view that formats dates like
> Mar 26, 2003
> May 06, 2006
> Jun 05, 2006
> Example
> SELECT DISTINCT TOP 5000 createDate, responsibilities
> FROM vw_someview
> ORDER BY createDate DESC,responsibilities
> the order by doesnt come out right. The view select for the field is
> convert( varchar(50), pp.createDate, 107 ) as createDate,
> It is stored as 2003-03-26 14:29:41.880
> Id like to order by properly but was wondering if I can do another
> convert to make it order by properly?
> Thanks|||Brian,
Sounds to me like your view needs changing, to report the date as a date.
How about leaving the formatting until you need to display it?
Currently, you're converting the date into a string and then querying that,
and then trying to sort by the result of a function on that string. That's
messy. Instead, let your view report the date itself, and tell whatever uses
that view to display the date in an appropriate format. If you really need
the view to report the date as string, then put it in a different column.
Of course, refactoring like this will be so much nicer in Visual Studio Team
Edition for Database Professionals, because you should be able to rename a
column in a view (to, say, CreateDateFormatted), and have it run through
your solution and do all the renaming for you.
Rob
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149525868.872085.191850@.i40g2000cwc.googlegroups.com...
> use order by convert(datetime,CreateDate)
> take a look at these 2 example
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by CreateDate
>
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by convert(datetime,CreateDate)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Brian wrote:
>

Order by clause help

Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:

>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Order by clause help

Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
Try:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegrou ps.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:

>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Order by clause help

Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:
>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP

Oracle OLEDB and dates not matching

I have a ssas2005 cube built from data in an oracle data warehouse. The Time dimension has begin week dates as a key and joins to the fact table Week Begin Date field. Both are built in the dsv using named queries. The cube built fine until we changed the provider to the Oracle provider for OLEDB. Now the cube build is giving an error : "The attribute key cannot be found: Table: Fact_x0020_Service_x0020_Level, Column: WEEK_BEGIN_DATE, Value: 7/3/2005." on the first record. The dates look the same and the properties look the same. I tried converting the dates to character in the queries and the cube builds - but I have a lot of data missing. We changed the provider because of internal rounding problems throwing the numbers off. Why is this happening? Is there a way to fix this?

The answer is trivial but situation overall quite confusing.

The implementation of OLEDB providers to the same relational datbase varies quite a lot. The changes in functionality happen from one version of OLEDB provider to another, the differences are even greater between OLEDB providers implemented by different companies.

In short: You could think that changing OLEDB provider in the conneciton string is a trivial, but in the reality it is not so at all. If this is absolutely neccessary that you use another OLEDB provider, you should work through every processing error.

Hope that helps

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I've been able to resolve all the problems but the date. It seems the issue is in the internal representation of the date between the .Net provider and SSAS - and there's no way i know of to fix the problem without converting the date to some other format - which causes other problems down the line with my date functions. Anybody have an idea on getting around this?

Oracle OLEDB and dates not matching

I have a ssas2005 cube built from data in an oracle data warehouse. The Time dimension has begin week dates as a key and joins to the fact table Week Begin Date field. Both are built in the dsv using named queries. The cube built fine until we changed the provider to the Oracle provider for OLEDB. Now the cube build is giving an error : "The attribute key cannot be found: Table: Fact_x0020_Service_x0020_Level, Column: WEEK_BEGIN_DATE, Value: 7/3/2005." on the first record. The dates look the same and the properties look the same. I tried converting the dates to character in the queries and the cube builds - but I have a lot of data missing. We changed the provider because of internal rounding problems throwing the numbers off. Why is this happening? Is there a way to fix this?

The answer is trivial but situation overall quite confusing.

The implementation of OLEDB providers to the same relational datbase varies quite a lot. The changes in functionality happen from one version of OLEDB provider to another, the differences are even greater between OLEDB providers implemented by different companies.

In short: You could think that changing OLEDB provider in the conneciton string is a trivial, but in the reality it is not so at all. If this is absolutely neccessary that you use another OLEDB provider, you should work through every processing error.

Hope that helps

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I've been able to resolve all the problems but the date. It seems the issue is in the internal representation of the date between the .Net provider and SSAS - and there's no way i know of to fix the problem without converting the date to some other format - which causes other problems down the line with my date functions. Anybody have an idea on getting around this?