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

Hi everyone,
I have a select statement of the form
SELECT * FROM temp ORDER BY time

I have a scalar function ConvertToMinutes that takes in varchar and returns int, is there any way to do something like this SELECT * FROM temp ORDER BY ConvertToMinutes(time). I tried doing this and it doesn't work (it tells me ConvertToMinutes is not a built-in function). Please guide me as to how I would accomplish this. Thanks in advance.

P.S. Clarification: I am trying to order the table temp by the value returned by the function ConvertToMinutes on the coloumn time.I am not exactly sure if you can use function in the order by clause. try

SELECT * FROM temp ORDER BY dbo.ConvertToMinutes(convert(varchar,time))

|||

I do believe ndinakar's method will work, however, if it does not, you can also do this:

SELECT *
FROM (SELECT *,dbo.converttominutes(time) mytime FROM temp) t1
ORDER BY mytime

or this:

SELECT temp.*
FROM temp
JOIN (SELECT DISTINCT time,dbo.converttominutes(time) mytime FROM temp) t1 ON (temp.time=t1.time)
ORDER BY mytime

|||Hey guys thanks for the answers. I tried the first solution and it worked, so I did not try the second one. But thanks to both of you for answering.sql

Oracle Translate function equivalent in SQL Server

Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.

Hi,

no there is no quivalent for the translate function.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Well, it is "easy" enough to replicate. Easy being relative of course :) You could use the CLR in 2005, which would probably be my suggestion, but another way is to stack replaces and substrings.

I of course no nothing about Oracle, so I used the following as a guide:

http://www.techonthenet.com/oracle/functions/translate.php

--translate('1tech23', '123', '456); would return '4tech56'
--translate('222tech, '2ec', '3it'); would return '333tith'

declare @.value varchar(100)
declare @.replace varchar(3) --you could support many many more if you want
--I have nested 200+ replace commands and still get
--snappy enough results
declare @.replaceWith varchar(3)

set @.value = '1tech23'
set @.replace = '123'
set @.replaceWith = '456'

select replace(replace(replace(@.value,substring(@.replace,1,1),substring(@.replaceWith,1,1)),substring(@.replace,2,1),substring(@.replaceWith,2,1)),substring(@.replace,3,1),substring(@.replaceWith,3,1))

Your example was:

set @.value = 'entertain'
set @.replace = 'et'
set @.replaceWith = 'ab'

select replace(replace(replace(@.value,substring(@.replace,1,1),substring(@.replaceWith,1,1)),substring(@.replace,2,1),substring(@.replaceWith,2,1)),substring(@.replace,3,1),substring(@.replaceWith,3,1))

This returns:

anbarbain

If you aren't making heavy duty use of this, a T-SQL function will work to genericise it:

create function dbo.translate
(
@.value varchar(max),
@.replace varchar(3),
@.replaceWith varchar(3)
) returns varchar(max) as

begin
return (replace(replace(replace(@.value,substring(@.replace,1,1),substring(@.replaceWith,1,1)),substring(@.replace,2,1),substring(@.replaceWith,2,1)),substring(@.replace,3,1),substring(@.replaceWith,3,1)))
end
go

select dbo.translate('entertain', 'et','ab')
go

Just code as many replace/substring command pairs as you allow by the size of the replace parameter.

Oracle Translate function equivalent in SQL Server

Hi

I want to know the equivalent of the Oracle translate function in SQL Server.

eg : select translate('entertain', 'et', 'ab') from dual.

I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.

Please let me know if there is some other equivalent function in SQL Server

thanks.CASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp) works wonders.

-PatP|||oh, i would just love to see how CASE works in this, er, um, case

care to share the example for TRANSLATE('entertain', 'et', 'ab'), pat?|||No. I'm tired, cranky, and trying to help. What are you doing to help?

-PatP|||What are you doing to help?subtly trying to inform the original poster that pursuing CASE may be a waste of time until he sees an actual example (which i am having a hard time conceiving)

i merely tried to match the degree of subtlety in my reply to yours

:)|||Maybe I am being a thicky pants but do nested REPLACEs not do the trick?|||thicky pants?

yes, that's the way i'd do it, as many nested REPLACE functions as characters to be translated

here's a classic example of TRANSLATE being used for simple encryption --

SELECT TRANSLATE(mycolumn,'abcdefghijklmnopqrstuvxyz', '5869413270plokij^#jm![edxc')|||Gotta love that Friday Feeling:
CREATE FUNCTION dbo.Thicky_Pants
(
@.Input AS VarChar(1000),
@.Find AS VarChar(100),
@.Replace AS VarChar(100)
)
RETURNS VarChar(1000)
AS
BEGIN

DECLARE @.i AS TinyInt

SELECT @.i = 1

WHILE @.i <= LEN(@.Find) BEGIN

SELECT @.Input = REPLACE(@.Input, SUBSTRING(@.Find, @.i, 1), SUBSTRING(@.Replace, @.i, 1))
SELECT @.i = @.i + 1

END
RETURN @.Input
END
GO

DECLARE @.String AS VarChar(1000)

SELECT @.String = 'pootle_flump'
SELECT @.String = dbo.Thicky_Pants(@.String, 'pt', 'xz')

PRINT @.String
xoozle_flumx|||here's a classic example of TRANSLATE being used for simple encryption --

SELECT TRANSLATE(mycolumn,'abcdefghijklmnopqrstuvxyz', '5869413270plokij^#jm![edxc')

Unless you are developing an application to write cryptograms for the Sunday paper, I hope nobody would use such an algorithm.

TRANSLATE is one of the goofier built-in functions in Oracle. The laser was once described as "a solution in search of a problem". Maybe someday there will be a practical use for TRANSLATE as well. In 10 years of SQL Server programming I've never found a need for such a function.|||In 10 years of SQL Server programming I've never found a need for such a function.well, there's your answer -- you need it all the time in oracle :)|||Somehow I've manager to get through a few Oracle projects without using it.

Oracle PL/SQL Date Function

Hi,
Can someone tell me how to write a date function for the following:
AsOfDate = If Monday, current date - 3
Else current date - 1
The date needs to be displayed in mm/dd/yyyy format.
Any help is greatly appreciated.Using DECODE:

DECODE( TO_CHAR(SYSDATE,'DY'), 'MON', SYSDATE-3, SYSDATE-1 )

Using CASE:

CASE WHEN TO_CHAR(SYSDATE,'DY')='MON' THEN SYSDATE-3 ELSE SYSDATE-1 END

In either case the result is of type DATE: use TO_CHAR to convert to required format when displaying.