Oracle DBA to learn MS SQL: date

Showing posts with label date. Show all posts

Friday, March 30, 2012

Order Issue

have a character field that represents a date in the format YYYYMM.
This data is broken down in three month blocks,
12 Dec
11 Nov
10 Oct
by this year and the corresponding 3 month block from last year.
So the data will look like this
200512
200511
200510
200412
200411
200410
I have a report that requires this order, but my result set is already being
put into another order and I want to know if I can convert the current YYYYM
M
format to a valid date.
Can this be done and how?
Thank you~~I'm not sure I follow you. If you have:
ORDER BY thecolumn DESC
don't you get the desired order? Also, why don't you store these as a smalld
atetime column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony W DiGrigoli" <AnthonyWDiGrigoli@.discussions.microsoft.com> wrote in
message
news:6368A787-C7D5-4DBA-B0DE-4E7D2B6473D3@.microsoft.com...
> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~|||You can use multiple expressions in the "order by" clause.
Example:
select c1, ..., cn
from table1
order by
cast(left(c1, 4) as int) desc, -- here you order by year
cast(right(c1, 2) as int) desc -- here by month
AMB
"Anthony W DiGrigoli" wrote:

> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~

Order Date prompt descending doesn't work

Hi,

In the report model I'm defining attribute date as ValueSelection: dropdown and SortDirection: Descending.

In the report builder I define the field as a prompt.

When I execute the report the values in the prompt date are order ascending and not descending.

This occur all over my model, can't order date field as ascending.

Any idea?

Thanks,

Assaf

In the report builder: There is a button "Sort and Group". Did you set the "Sort by" property of the date? I think that has to be set in order to sort right.|||

Hi,

Thanks for your reply.

The "Sort by" sort the records on the report.

My problem is that the sort in the prompt itself is always stay ascesnding although in the .NET I set it to descending

Any Idea?

Assaf

sql

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 with specific record on top

I have a routine that is displays my record in date order.
I want to be able to put a specific record from that set of rows on top,
with the rest in date order (as it is now, but without the one I moved to
the top.
For example, I have the following select:
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com'
order by datesubmitted desc
This gives me the following:
JobID jobTitle
_______ ________________________________________
__
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3731 Desktop Computer Support Technician/17Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
What I want to do is take Job ID 3731 and move it to the top like so:
JobID jobTitle
_______ ________________________________________
__
3731 Desktop Computer Support Technician/17Feb05
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
I tried using a union ( I am sure there is a better way):
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID = 3731
union
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID <> 3731
order by datesubmitted desc
I got an error:
Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a
UNION operator.
I put datesubmitted in the select statment and got the following:
JobID jobTitle
_______ ________________________________________
__
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3731 Desktop Computer Support Technician/17Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
I assume it did the union and then order by, which negated what I was trying
to do.
How do I do the order by, just on the 2nd select and have it put the 2
together?
Thanks,
Tom"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OEK6BxSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
>I have a routine that is displays my record in date order.
> I want to be able to put a specific record from that set of rows on top,
> with the rest in date order (as it is now, but without the one I moved to
> the top.
> For example, I have the following select:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by datesubmitted desc
> This gives me the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> What I want to do is take Job ID 3731 and move it to the top like so:
> JobID jobTitle
> _______ ________________________________________
__
> 3731 Desktop Computer Support Technician/17Feb05
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
>
> I tried using a union ( I am sure there is a better way):
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID = 3731
> union
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID <> 3731
> order by datesubmitted desc
> I got an error:
> Server: Msg 104, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if the statement contains a
> UNION operator.
> I put datesubmitted in the select statment and got the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> I assume it did the union and then order by, which negated what I was
> trying to do.
> How do I do the order by, just on the 2nd select and have it put the 2
> together?
Select 1 as temp,JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID = 3731
union
Select 2 as temp,JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID <> 3731
order by temp,datesubmitted desc
This seems to work, but was wondering if there was a better way than using 2
selects?
Thanks,
Tom|||>> This seems to work, but was wondering if there was a better way than
Use a CASE expression in your SELECT list & use its alias in the ORDER BY or
use a CASE expression directly in the ORDER BY clause. In your case, you can
have one like:
ORDER BY
CASE WHEN JobID = 3731 THEN 1 ELSE 2 END, datesubmitted DESC ;
Anith|||Try:
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com'
order by case when JobID=3731 then 0 else 1 end asc, datesubmitted desc
-oj
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OEK6BxSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
>I have a routine that is displays my record in date order.
> I want to be able to put a specific record from that set of rows on top,
> with the rest in date order (as it is now, but without the one I moved to
> the top.
> For example, I have the following select:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by datesubmitted desc
> This gives me the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> What I want to do is take Job ID 3731 and move it to the top like so:
> JobID jobTitle
> _______ ________________________________________
__
> 3731 Desktop Computer Support Technician/17Feb05
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
>
> I tried using a union ( I am sure there is a better way):
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID = 3731
> union
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID <> 3731
> order by datesubmitted desc
> I got an error:
> Server: Msg 104, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if the statement contains a
> UNION operator.
> I put datesubmitted in the select statment and got the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> I assume it did the union and then order by, which negated what I was
> trying to do.
> How do I do the order by, just on the 2nd select and have it put the 2
> together?
> Thanks,
> Tom
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ufKXI4SGFHA.208@.TK2MSFTNGP12.phx.gbl...
> Use a CASE expression in your SELECT list & use its alias in the ORDER BY
> or use a CASE expression directly in the ORDER BY clause. In your case,
> you can have one like:
> ORDER BY
> CASE WHEN JobID = 3731 THEN 1 ELSE 2 END, datesubmitted DESC ;
That did it.
Thanks,
Tom|||"oj" <nospam_ojngo@.home.com> wrote in message
news:%232JQi4SGFHA.1932@.TK2MSFTNGP14.phx.gbl...
> Try:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by case when JobID=3731 then 0 else 1 end asc, datesubmitted desc
That did it.
Thanks,
Tom

Wednesday, March 28, 2012

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 problem

Hi there...

If I have a table with two differnet DateTime variables, and I want to order the hits in date order. The problem is that I want to order them depending of wich of theese two columns are the highest(eg. latest date)

ex:
id | date_1 | date_2
------
1 | 1991 | 2001
2 | 2002 | 1991
3 | 1993 | 1992

should result in(highest first):
id
--
2
1
3

How would I do this?If your DBMS has a function GREATEST (or similar) then:

ORDER BY GREATEST (date_1, date_2)

If not, you can do it using CASE:

ORDER BY CASE WHEN date_1 > date_2 THEN date_1 ELSE date_2 END|||tony, you forgot the DESC

http://www.dbforums.com/t999138.html

zcumbag, please do not cross-post|||tony, you forgot the DESC
Thanks, Rudy!|||thanks... works fine...

sorry about the cross-posting. I missread thestartpage... wont happen again... thanks|||OK now I deicovered another problem... I need to SELECT DISTINCT and because of that I can't have the CASE-statement in the ORDER BY-statement...

I need to find another solution...

is it posible to have the datestatement in the SELECT-line?
something like this:

SELECT DISTINCT TOP 10 ArticleId, Heading, WICHEVERISBIGGEST(date1, date2) AS date3 FROM tblARticles
...
ORDER BY date3

I haven't seem to find any WICHEVERISBIGGEST-kindof function...

anyone?|||Well, if your DBMS doesn't have a "WHICHEVERISBIGGEST" function (in Oracle it is called GREATEST), then again CASE will do it:

SELECT DISTINCT TOP 10 ArticleId, Heading, CASE WHEN date1 > date2 THEN date1 ELSE date2 END AS date3|||I need to SELECT DISTINCT and because of that I can't have the CASE-statement in the ORDER BY-statement...

can you show us the query? i don't see why DISTINCT means that you can't put a CASE expression into the ORDER BY

you can also say ORDER BY 4 (where 4 is the 4th column) if that helps|||can you show us the query? i don't see why DISTINCT means that you can't put a CASE expression into the ORDER BY

The reason is Error 145 wich says Order By items must appear in the select list if Select Distinct is specified.

But it works if I put the case statement in the select line.

thankssql

Monday, March 26, 2012

ORDER BY Issue on funky field names

Hello,
I am using FOR XML EXPLICIT

Problem is, I need to sort by [MyColumn!1!MyCol].

This column contains date in string format, And I want it to be sorted as if it was a date.

so I tried this

ORDER BY CONVERT(DATETIME, [MyColumn!1!MyCol])

it gives me error that ORDER BY items must be in select list
The whole query is actually a UNION of 2 queries

Please help me with this

Thanks,Order by has problems with column aliases. So try:
ORDER BY CONVERT(DATETIME, <statement for column value>)
i you have
...
, my_string_date+' '+my_string_time as [MyColumn!1!MyCol]
....

use

ORDER BY CONVERT(DATETIME, my_string_date+' '+my_string_time)

ORDER BY Issue on funky field names

Hello,
I am using FOR XML EXPLICIT

Problem is, I need to sort by [MyColumn!1!MyCol].

This column contains date in string format, And I want it to be sorted as if it was a date.

so I tried this

ORDER BY CONVERT(DATETIME, [MyColumn!1!MyCol])

it gives me error that ORDER BY items must be in select list
The whole query is actually a UNION of 2 queries

Please help me with this

Thanks,Order by has problems with column aliases. So try:
ORDER BY CONVERT(DATETIME, <statement for column value>)
i you have
...
, my_string_date+' '+my_string_time as [MyColumn!1!MyCol]
....

use

ORDER BY CONVERT(DATETIME, my_string_date+' '+my_string_time)

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

Friday, March 23, 2012

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 date and time of insert

I just wanted to know is there any way to order the rows of table based on date and time of insert. I dont have date column in my table. I have to insert values into the table. Based on the system date and time of the values inserted into the table, can i order the rows??As you have posted a question in the articles section it is being moved to SQL Server Forum .

MODERAOTR.|||for that you need to add a new column to the table and store system date and time in that column for every new insert and finally sort the data by that column.

order by cluase cause wrong results to be returned.

I have the follow table.

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

If I use the following SQL:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3

GET Following correct results:

Adair 284 899989594 0 574857716 190479902 1665327212 0 0 0 0 1665327212 0 1 United States 1 1

IF I use the following SQL I get the wrong results:

WRONG results:

Adair 74 81733110 0 49616018 24671651 156020779 50510500 0 0 0 203870779 0 1 United States 50 1 Adair 437 1468698657 0 495479839 353202768 2317381264 12984266 0 0 0 2315676030 0 1 United States 25 1 Adair 1813 20309722045 0 6597005374 4253819645 31160547064 43636703 0 0 0 31135010742 0 1 United States 11 1 Adair 606 439581417 0 331746662 132240332 903568411 0 0 0 0 903568411 0 1 United States 45 1 Adair 236 350256381 0 524269553 504973831 1379499765 4080368 0 0 0 1380473415 0 1 United States 23 1

etc.....

In what way do you think the results are wrong? Do you mean that the Themename values are not ordered correctly?

If so, then remember that when you have a column which has duplicate entries, there is nothing to tell sql server to output them in any particular order. You are just ordering your records based upon the value of that column ony. If you want duplicate entries to be ordered by intLocationCount, you'll need to specify that in your order clause too.

Eg ORDER BY ThemeName, intLocationCount DESC

HTH!

|||

when the themename is used I get multiple rows when the temp table contains only one row for each themename.

themename is based on the level2 and level3 values and there is only one row for every level2 and level3 combination.

in this case level2 = state code and level3 = county code. Themename should be county name.

If you can tell me how to send you the under lying table I will.

|||

This is not possible, the order by clause does not affect the number of rows returned merely their ordering.

More records must have been inserted into the underlying table after the initial query was run and before the subsequent query was run.

|||

This a a very repeatable problem can I send you the underlying table? I dump the temp table to a perm table to check the results and I got the same behovior eventhough when you a select * from the table you only get one row from the table for each themename.

I agree this should never happen especial since its a single table no join.....

|||

Since you said there is only one row, I reverse-engineered it and created the following:

Code Snippet

USE tempdb;

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[deletethisTempOut](

[ThemeName] [varchar](60) NULL,

[intLocationCount] [int] NULL,

[dblRepValueA] [float] NULL,

[dblRepValueB] [float] NULL,

[dblRepValueC] [float] NULL,

[dblRepValueD] [float] NULL,

[dblTotalRepValue] [float] NULL,

[dblLimit1] [float] NULL,

[dblLimit2] [float] NULL,

[dblLimit3] [float] NULL,

[dblLimit4] [float] NULL,

[dblTotalLimit] [float] NULL,

[fltEmployeecount] [float] NULL,

[intAreaLevel1] [tinyint] NOT NULL,

[strFullName] [varchar](13) NOT NULL,

[strAreaLevel2] [varchar](20) NOT NULL,

[strAreaLevel3] [varchar](20) NOT NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1)

-- Query 1

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY strAreaLevel2, strAreaLevel3

-- Query 2

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY ThemeName

I couldn't repro the problem you are having.

Is this similar to what you have? Are you missing out a WHERE clause in one of the queries?

|||

Caveman,

You'll have to provide more information. You have mentioned a temp table, but the code you posted does not refer to a temp table. Later you said something about a permanent table and a temp table, again without being specific about anything.

If I had to guess (which I do in this case), my suspicion is that perhaps you are seeing problems SELECTing from a view where the view definition contains things like UNION, TOP, or ORDER BY. It is possible, however, that you are encountering a bug.

Can you run SELECT @.@.VERSION and either post the result or find out whether you have installed the latest service pack for the version of SQL Server you're running?

It probably won't help to send anyone the data in the underlying table. What we need to see to help is the *exact* queries that you have problems with, and all underlying definitions. For example, if the problem query involves a temporary table, we need to see the code that inserts data into the temporary table. If there is a view somewhere, we need to see its definition.

Steve Kass

Drew University

http://www.stevekass.com

order by cluase cause wrong results to be returned.

I have the follow table.

GO
SET ANSI_PADDING OFF

If I use the following SQL:

GET Following correct results:

Adair 284 899989594 0 574857716 190479902 1665327212 0 0 0 0 1665327212 0 1 United States 1 1

IF I use the following SQL I get the wrong results:

WRONG results:

etc.....

In what way do you think the results are wrong? Do you mean that the Themename values are not ordered correctly?

Eg ORDER BY ThemeName, intLocationCount DESC

HTH!

|||

when the themename is used I get multiple rows when the temp table contains only one row for each themename.

themename is based on the level2 and level3 values and there is only one row for every level2 and level3 combination.

in this case level2 = state code and level3 = county code. Themename should be county name.

If you can tell me how to send you the under lying table I will.

|||

This is not possible, the order by clause does not affect the number of rows returned merely their ordering.

More records must have been inserted into the underlying table after the initial query was run and before the subsequent query was run.

|||

I agree this should never happen especial since its a single table no join.....

|||

Since you said there is only one row, I reverse-engineered it and created the following:

Code Snippet

USE tempdb;

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[deletethisTempOut](

[ThemeName] [varchar](60) NULL,

[intLocationCount] [int] NULL,

[dblRepValueA] [float] NULL,

[dblRepValueB] [float] NULL,

[dblRepValueC] [float] NULL,

[dblRepValueD] [float] NULL,

[dblTotalRepValue] [float] NULL,

[dblLimit1] [float] NULL,

[dblLimit2] [float] NULL,

[dblLimit3] [float] NULL,

[dblLimit4] [float] NULL,

[dblTotalLimit] [float] NULL,

[fltEmployeecount] [float] NULL,

[intAreaLevel1] [tinyint] NOT NULL,

[strFullName] [varchar](13) NOT NULL,

[strAreaLevel2] [varchar](20) NOT NULL,

[strAreaLevel3] [varchar](20) NOT NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1)

-- Query 1

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY strAreaLevel2, strAreaLevel3

-- Query 2

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY ThemeName

I couldn't repro the problem you are having.

Is this similar to what you have? Are you missing out a WHERE clause in one of the queries?

|||

Caveman,

Can you run SELECT @.@.VERSION and either post the result or find out whether you have installed the latest service pack for the version of SQL Server you're running?

Steve Kass

Drew University

http://www.stevekass.com

sql

order by cluase cause wrong results to be returned.

I have the follow table.

GO
SET ANSI_PADDING OFF

If I use the following SQL:

GET Following correct results:

Adair 284 899989594 0 574857716 190479902 1665327212 0 0 0 0 1665327212 0 1 United States 1 1

IF I use the following SQL I get the wrong results:

WRONG results:

etc.....

In what way do you think the results are wrong? Do you mean that the Themename values are not ordered correctly?

Eg ORDER BY ThemeName, intLocationCount DESC

HTH!

|||

when the themename is used I get multiple rows when the temp table contains only one row for each themename.

themename is based on the level2 and level3 values and there is only one row for every level2 and level3 combination.

in this case level2 = state code and level3 = county code. Themename should be county name.

If you can tell me how to send you the under lying table I will.

|||

This is not possible, the order by clause does not affect the number of rows returned merely their ordering.

More records must have been inserted into the underlying table after the initial query was run and before the subsequent query was run.

|||

I agree this should never happen especial since its a single table no join.....

|||

Since you said there is only one row, I reverse-engineered it and created the following:

Code Snippet

USE tempdb;

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[deletethisTempOut](

[ThemeName] [varchar](60) NULL,

[intLocationCount] [int] NULL,

[dblRepValueA] [float] NULL,

[dblRepValueB] [float] NULL,

[dblRepValueC] [float] NULL,

[dblRepValueD] [float] NULL,

[dblTotalRepValue] [float] NULL,

[dblLimit1] [float] NULL,

[dblLimit2] [float] NULL,

[dblLimit3] [float] NULL,

[dblLimit4] [float] NULL,

[dblTotalLimit] [float] NULL,

[fltEmployeecount] [float] NULL,

[intAreaLevel1] [tinyint] NOT NULL,

[strFullName] [varchar](13) NOT NULL,

[strAreaLevel2] [varchar](20) NOT NULL,

[strAreaLevel3] [varchar](20) NOT NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1)

-- Query 1

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY strAreaLevel2, strAreaLevel3

-- Query 2

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY ThemeName

I couldn't repro the problem you are having.

Is this similar to what you have? Are you missing out a WHERE clause in one of the queries?

|||

Caveman,

Can you run SELECT @.@.VERSION and either post the result or find out whether you have installed the latest service pack for the version of SQL Server you're running?

Steve Kass

Drew University

http://www.stevekass.com

Friday, March 30, 2012

Order Issue

Order Date prompt descending doesn't work

Order converted dates in union query

Order by with specific record on top

Wednesday, March 28, 2012

ORDER BY problem with CONVERT

Order by problem

Order by problem

Monday, March 26, 2012

ORDER BY Issue on funky field names

ORDER BY Issue on funky field names

ORDER BY earliest date in a row

Friday, March 23, 2012

Order By Date Format

Order By date and time of insert

order by cluase cause wrong results to be returned.

order by cluase cause wrong results to be returned.

order by cluase cause wrong results to be returned.

Oracle DBA to learn MS SQL

Blog Archive

About Me