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,
How can I order the column in the correct order if my numbers are in string
fields? I am now getting
1
11
10
etc...
Regards,you could always convert/CAST the field in to numerics and sort on this
column?
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I order the column in the correct order if my numbers are in
string
> fields? I am now getting
> 1
> 11
> 10
> etc...
> Regards,
>
>|||Hi
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('11')
INSERT INTO #Test VALUES ('10')
SELECT * FROM #Test ORDER BY col ASC
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I order the column in the correct order if my numbers are in
> string
> fields? I am now getting
> 1
> 11
> 10
> etc...
> Regards,
>
>|||I f you are sure that only numeric data is inserted you could cast this
as follow to an INT (or any other numeric type)
Create Table #temp
(
Col varchar(10)
)
INSERt INTO #Temp
Select '1'
INSERt INTO #Temp
Select '11'
INSERt INTO #Temp
Select '10'
Select * from #Temp order by col
Select * from #Temp order by CAST(col AS INT)
Drop table #temp
HTH, Jens Suessmeyer.|||I f you are sure that only numeric data is inserted you could cast this
as follow to an INT (or any other numeric type)
Create Table #temp
(
Col varchar(10)
)
INSERt INTO #Temp
Select '1'
INSERt INTO #Temp
Select '11'
INSERt INTO #Temp
Select '10'
Select * from #Temp order by col
Select * from #Temp order by CAST(col AS INT)
Drop table #temp
HTH, Jens Suessmeyer.|||Ricky
Yes , but what if he has a literal characters in the column as well. CAST
conversion will fail
"Ricky" <MSN.MSN.com> wrote in message
news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> you could always convert/CAST the field in to numerics and sort on this
> column?
> "EDom" <technical@.peoplewareindia.com> wrote in message
> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> How can I order the column in the correct order if my numbers are in
> string
>> fields? I am now getting
>> 1
>> 11
>> 10
>> etc...
>> Regards,
>>
>|||Hi,
I do have char attached to the numbers.
A1, A11, A10, A11B, A21C
etc
Regards,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK7ROcpwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Ricky
> Yes , but what if he has a literal characters in the column as well. CAST
> conversion will fail
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> > you could always convert/CAST the field in to numerics and sort on this
> > column?
> >
> > "EDom" <technical@.peoplewareindia.com> wrote in message
> > news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How can I order the column in the correct order if my numbers are in
> > string
> >> fields? I am now getting
> >> 1
> >> 11
> >> 10
> >> etc...
> >>
> >> Regards,
> >>
> >>
> >>
> >
> >
>|||Good Point Uri!!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK7ROcpwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Ricky
> Yes , but what if he has a literal characters in the column as well. CAST
> conversion will fail
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> > you could always convert/CAST the field in to numerics and sort on this
> > column?
> >
> > "EDom" <technical@.peoplewareindia.com> wrote in message
> > news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How can I order the column in the correct order if my numbers are in
> > string
> >> fields? I am now getting
> >> 1
> >> 11
> >> 10
> >> etc...
> >>
> >> Regards,
> >>
> >>
> >>
> >
> >
>|||Hi,
I dont get it correct
1A
11A
10A
this gives me the same result even I do sorting.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#YowWYpwFHA.664@.tk2msftngp13.phx.gbl...
> Hi
> CREATE TABLE #Test
> (
> col VARCHAR(10)
> )
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('11')
> INSERT INTO #Test VALUES ('10')
> SELECT * FROM #Test ORDER BY col ASC
>
> "EDom" <technical@.peoplewareindia.com> wrote in message
> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > How can I order the column in the correct order if my numbers are in
> > string
> > fields? I am now getting
> > 1
> > 11
> > 10
> > etc...
> >
> > Regards,
> >
> >
> >
>|||Hi
SELECT * FROM #Test ORDER BY right('0000'+col,4) ASC
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eFapS40wFHA.2656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I dont get it correct
> 1A
> 11A
> 10A
> this gives me the same result even I do sorting.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#YowWYpwFHA.664@.tk2msftngp13.phx.gbl...
>> Hi
>> CREATE TABLE #Test
>> (
>> col VARCHAR(10)
>> )
>> INSERT INTO #Test VALUES ('1')
>> INSERT INTO #Test VALUES ('11')
>> INSERT INTO #Test VALUES ('10')
>> SELECT * FROM #Test ORDER BY col ASC
>>
>> "EDom" <technical@.peoplewareindia.com> wrote in message
>> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
>> > Hi,
>> >
>> > How can I order the column in the correct order if my numbers are in
>> > string
>> > fields? I am now getting
>> > 1
>> > 11
>> > 10
>> > etc...
>> >
>> > Regards,
>> >
>> >
>> >
>>
>

Oracle SQLPlus Worksheet

When I run an SQLPlus query, the displayed width of each column is the width of the data item. This means that the column header for many fields is truncated. How do I override this default width in order to make the entire column header appear without being truncated. An example is below. The last column is titled DESTINATION, but only DES shows.

BAG_TAG_N ORI DES
--- -- --
3UA233468 TUL ORD
3UA233468 AVP ORD
3UA233468 ORD MLIOriginally posted by mryan916
When I run an SQLPlus query, the displayed width of each column is the width of the data item. This means that the column header for many fields is truncated. How do I override this default width in order to make the entire column header appear without being truncated. An example is below. The last column is titled DESTINATION, but only DES shows.

BAG_TAG_N ORI DES
--- -- --
3UA233468 TUL ORD
3UA233468 AVP ORD
3UA233468 ORD MLI

try: set linesize 120

Oracle OLEDB drivers problem with Numbers

Hi All:

I am using oracle oledb drivers to write to a oledb destination.

if i give decimal values to decimal fields in the source table, i get the same in destination. But if the input is integers, in some cases, the value in the destination is different from that of source

Source Target

50 50.00
100 0.000
111 111.000
600 0.0000
520 20.00
178 178
4546.50 4546.50

I have Sql server SP2 9.0.3042 installed on my machine. Please let me know if theres something i am missing out.

Thanks,

Vipul

There is no such thing as an integer in Oracle. It's a NUMERIC(p,0) field. That is, it has no scale. SSIS doesn't support this at the moment. Instead, write your query such that you convert the "integer" field into a NUMERIC(p+1,1) field, or something like that. Then map to a decimal field in SSIS. From there, if you want integers out of the data, use a derived column to cast the values to integers.|||

Let me put it this way phil. Have you come across decimal data being changed from source to target without any transforms in between ? I was not correct in putting the question but the cause of my concern is that if the source has 500 how the target is getting it as 0.

Is there some problem in SSIS for this or this is oracle oledb driver problem?

|||Have you looked at the data with a data viewer to see what is contained there? You might need to recreate the OLE DB source.|||

ya i have viewed data with the data viewer before the oledb destination. Data is fine till data viewer. Theres something happening in oledb destination and thats why i suspect the drivers.

Also, the same behaviour is not happening on one of my other machine. The machine confguration of both the machine are same. I am executing the same pacakge from both the machines.

The version of software on both mahcine are:

-

SQL Server sp2 9.0.3042

Oracle 10g

Let me know your thoughts on this..

|||Is your destination SQL Server?

Have you looked at the advanced properties of the OLE DB Destination to ensure that the data types for all of the columns are correct?|||

Destination is Oracle.

And i have checked all the datatypes as per ur suggestion but still the problem exists.

|||I'm going to have to bow out as I don't have an Oracle instance to test with.

oracle date conversion

is really bugging me...
Found out that some date fields wont' be allowed on sql server so my syntax
is
select * from openquery('oracleserver', 'select column1, column2,
to_char(date, 'yyyy/mm/dd') as date from table')
this works fine when selecting but when inserting this result into a table
on sql server I'm in trouble
insert into table xx
(columns...)
select * from openquery('oracleserver', 'select column1, column2,
to_char(date, 'yyyy/mm/dd') as date from table')
I get an error saying use ROBUST PLAN.
then i put on Robust plan and it says The query processer could not produce
a query plan
Then I found this article that says post you troubles here...? Anyone ?
My next attempt would be to avoid linked server and use transform data task
in a DTS package to see if that helps
http://www.aspfaq.com/show.asp?id=2400Perhaps a language neutral datetime format will work better. Try converting
to the format yyyymmdd.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"michael v" <test@.test.com> wrote in message news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...

> is really bugging me...
> Found out that some date fields wont' be allowed on sql server so my synta
x
> is
> select * from openquery('oracleserver', 'select column1, column2,
> to_char(date, 'yyyy/mm/dd') as date from table')
> this works fine when selecting but when inserting this result into a table
> on sql server I'm in trouble
> insert into table xx
> (columns...)
> select * from openquery('oracleserver', 'select column1, column2,
> to_char(date, 'yyyy/mm/dd') as date from table')
> I get an error saying use ROBUST PLAN.
> then i put on Robust plan and it says The query processer could not produc
e
> a query plan
> Then I found this article that says post you troubles here...? Anyone ?
> My next attempt would be to avoid linked server and use transform data tas
k
> in a DTS package to see if that helps
> http://www.aspfaq.com/show.asp?id=2400
>|||Thanx for the reply but found out that it wasn't the date conversion at all.
It was a column with 4000 characters as then lenght.
When not trying to insert this column it works fine...
What to do ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOH5aqv0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Perhaps a language neutral datetime format will work better. Try
converting to the format yyyymmdd.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "michael v" <test@.test.com> wrote in message
news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...
syntax
table
produce
task|||Ahh, that explains the error message. You could try adding some substring to
short the number of
characters returned, I guess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"michael v" <test@.test.com> wrote in message news:eqzHfvv0FHA.1028@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> Thanx for the reply but found out that it wasn't the date conversion at al
l.
> It was a column with 4000 characters as then lenght.
> When not trying to insert this column it works fine...
> What to do ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uOH5aqv0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> converting to the format yyyymmdd.
> news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...
> syntax
> table
> produce
> task
>[/color]|||thanx
but I found an article mentioning trouble with row size limit / varchar
fields.
i changed the field from varchar to text and it worked.
Is there something I should now about the text field type ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#Wgp2vy0FHA.3892@.TK2MSFTNGP12.phx.gbl...
> Ahh, that explains the error message. You could try adding some substring
to short the number of
> characters returned, I guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "michael v" <test@.test.com> wrote in message
news:eqzHfvv0FHA.1028@.TK2MSFTNGP12.phx.gbl...
all.
in
?
data
>