order data

Hi all,
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
wa
The obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or a character
representation which sorts correctly), but we need to see what format you have for your date values
first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegr oups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>
|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa
|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegr oups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>
|||Thanks a lots!
I'm new on sql language.

order data

Hi all,
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
waThe obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or a character
representation which sorts correctly), but we need to see what format you have for your date values
first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegroups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegroups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>|||Thanks a lots!
I'm new on sql language.

order data

Hi all,
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
waThe obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or
a character
representation which sorts correctly), but we need to see what format you ha
ve for your date values
first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegroups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegroups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>|||Thanks a lots!
I'm new on sql language.

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?You Could Try

Select Date_1 as UDate FROM table
UNION
Select Date_2 as UDate FROM table
ORDER By UDate DESC|||ORDER BY case when date1 > date2 then date1 else date2 end|||ORDER BY case when date1 > date2 then date1 else date2 end

ORDER BY case when d1 > d2 then d1 else d2 end DESC

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

ORDER BY DATETIME field BUG?

I was under the impression that the DATETIME field was stored
internally in SQL server as
two integers?
Any idea how to get correct ordering of a datetime field?
I have two records containing a datetime field with these values and
this is the order that
order by displays them in when I say "select * from blah ORDER BY
datetimefield"
Dec 30 1989 11:55PM
Dec 31 1989 1:05PM
Dec 31 1989 7:15AM
Dec 31 1989 7:35PM
Dec 31 1989 9:10AM
Dec 31 1989 10:20AM
Dec 31 1989 11:10AM
Dec 31 1989 11:40AM
Jan 1 1990 1:20PM
Jan 1 1990 12:35PM
Why doesn't Order by on a datetime field sort correctly instead of
treating them like strings?Can you show us the result of sp_help tablename and/or the CREATE TABLE /
INSERT / SELECT to reproduce this problem? It looks like they are stored
and/or sorted as varchar, not datetime, or you are sorting the result of a
convert, not the column itself. (The most telltale thing is thathe result
does not contain seconds or milliseconds).
"wolfen42 at gmail.com" <wolfen42@.gmail.com> wrote in message
news:1150996807.502951.137230@.m73g2000cwd.googlegroups.com...
>I was under the impression that the DATETIME field was stored
> internally in SQL server as
> two integers?
> Any idea how to get correct ordering of a datetime field?
> I have two records containing a datetime field with these values and
> this is the order that
> order by displays them in when I say "select * from blah ORDER BY
> datetimefield"
> Dec 30 1989 11:55PM
> Dec 31 1989 1:05PM
> Dec 31 1989 7:15AM
> Dec 31 1989 7:35PM
> Dec 31 1989 9:10AM
> Dec 31 1989 10:20AM
> Dec 31 1989 11:10AM
> Dec 31 1989 11:40AM
> Jan 1 1990 1:20PM
> Jan 1 1990 12:35PM
> Why doesn't Order by on a datetime field sort correctly instead of
> treating them like strings?
>|||wolfen42 at gmail.com,

> Any idea how to get correct ordering of a datetime field?
Are you sure that column is a datetime one?. Seems like it is charecter data
type instead atetime. See next statements.
select
*
from
(
select cast('Dec 30 1989 11:55PM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 1:05PM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 7:15AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 7:35PM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 9:10AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 10:20AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 11:10AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 11:40AM' as varchar(25)) as c1 union all
select cast('Jan 1 1990 1:20PM' as varchar(25)) as c1 union all
select cast('Jan 1 1990 12:35PM' as varchar(25)) as c1
) as t1
order by
c1
select
*
from
(
select cast('Dec 30 1989 11:55PM' as datetime) as c1 union all
select cast('Dec 31 1989 1:05PM' as datetime) as c1 union all
select cast('Dec 31 1989 7:15AM' as datetime) as c1 union all
select cast('Dec 31 1989 7:35PM' as datetime) as c1 union all
select cast('Dec 31 1989 9:10AM' as datetime) as c1 union all
select cast('Dec 31 1989 10:20AM' as datetime) as c1 union all
select cast('Dec 31 1989 11:10AM' as datetime) as c1 union all
select cast('Dec 31 1989 11:40AM' as datetime) as c1 union all
select cast('Jan 1 1990 1:20PM' as datetime) as c1 union all
select cast('Jan 1 1990 12:35PM' as datetime) as c1
) as t1
order by
c1
go
AMB
"wolfen42 at gmail.com" wrote:

> I was under the impression that the DATETIME field was stored
> internally in SQL server as
> two integers?
> Any idea how to get correct ordering of a datetime field?
> I have two records containing a datetime field with these values and
> this is the order that
> order by displays them in when I say "select * from blah ORDER BY
> datetimefield"
> Dec 30 1989 11:55PM
> Dec 31 1989 1:05PM
> Dec 31 1989 7:15AM
> Dec 31 1989 7:35PM
> Dec 31 1989 9:10AM
> Dec 31 1989 10:20AM
> Dec 31 1989 11:10AM
> Dec 31 1989 11:40AM
> Jan 1 1990 1:20PM
> Jan 1 1990 12:35PM
> Why doesn't Order by on a datetime field sort correctly instead of
> treating them like strings?
>|||wolfen42 at gmail.com wrote:
> I was under the impression that the DATETIME field was stored
> internally in SQL server as
> two integers?
> Any idea how to get correct ordering of a datetime field?
> I have two records containing a datetime field with these values and
> this is the order that
> order by displays them in when I say "select * from blah ORDER BY
> datetimefield"
> Dec 30 1989 11:55PM
> Dec 31 1989 1:05PM
> Dec 31 1989 7:15AM
> Dec 31 1989 7:35PM
> Dec 31 1989 9:10AM
> Dec 31 1989 10:20AM
> Dec 31 1989 11:10AM
> Dec 31 1989 11:40AM
> Jan 1 1990 1:20PM
> Jan 1 1990 12:35PM
> Why doesn't Order by on a datetime field sort correctly instead of
> treating them like strings?
>
Are you sure that field is of type DATETIME? I just inserted these same
values into a table with a DATETIME field, and they sort properly. If I
put them into a VARCHAR field, then they do not.|||Can you provide a sample that works this way? It sorts finr for me.
CREATE TABLE JustADate
(Dt datetime)
INSERT JustADate VALUES('Dec 30 1989 11:55PM')
INSERT JustADate VALUES('Dec 31 1989 1:05PM')
INSERT JustADate VALUES('Dec 31 1989 7:15AM')
INSERT JustADate VALUES('Dec 31 1989 7:35PM')
INSERT JustADate VALUES('Dec 31 1989 9:10AM')
INSERT JustADate VALUES('Dec 31 1989 10:20AM')
INSERT JustADate VALUES('Dec 31 1989 11:10AM')
INSERT JustADate VALUES('Dec 31 1989 11:40AM')
INSERT JustADate VALUES('Jan 1 1990 1:20PM')
INSERT JustADate VALUES('Jan 1 1990 12:35PM')
SELECT *
FROM JustADate
ORDER BY Dt
Dt
--
1989-12-30 23:55:00.000
1989-12-31 07:15:00.000
1989-12-31 09:10:00.000
1989-12-31 10:20:00.000
1989-12-31 11:10:00.000
1989-12-31 11:40:00.000
1989-12-31 13:05:00.000
1989-12-31 19:35:00.000
1990-01-01 12:35:00.000
1990-01-01 13:20:00.000
Roy Harvey
Beacon Falls, CT
On 22 Jun 2006 10:20:07 -0700, "wolfen42 at gmail.com"
<wolfen42@.gmail.com> wrote:

>I was under the impression that the DATETIME field was stored
>internally in SQL server as
>two integers?
>Any idea how to get correct ordering of a datetime field?
>I have two records containing a datetime field with these values and
>this is the order that
>order by displays them in when I say "select * from blah ORDER BY
>datetimefield"
>Dec 30 1989 11:55PM
>Dec 31 1989 1:05PM
>Dec 31 1989 7:15AM
>Dec 31 1989 7:35PM
>Dec 31 1989 9:10AM
>Dec 31 1989 10:20AM
>Dec 31 1989 11:10AM
>Dec 31 1989 11:40AM
>Jan 1 1990 1:20PM
>Jan 1 1990 12:35PM
>Why doesn't Order by on a datetime field sort correctly instead of
>treating them like strings?|||Unless you do this of course
CREATE TABLE JustADate
(Dt datetime)
INSERT JustADate VALUES('Dec 30 1989 11:55PM')
INSERT JustADate VALUES('Dec 31 1989 1:05PM')
INSERT JustADate VALUES('Dec 31 1989 7:15AM')
INSERT JustADate VALUES('Dec 31 1989 7:35PM')
INSERT JustADate VALUES('Dec 31 1989 9:10AM')
INSERT JustADate VALUES('Dec 31 1989 10:20AM')
INSERT JustADate VALUES('Dec 31 1989 11:10AM')
INSERT JustADate VALUES('Dec 31 1989 11:40AM')
INSERT JustADate VALUES('Jan 1 1990 1:20PM')
INSERT JustADate VALUES('Jan 1 1990 12:35PM')
SELECT convert(varchar(20),dt,100) as Dt
FROM JustADate
ORDER BY Dt
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Roy Harvey wrote:
> Can you provide a sample that works this way? It sorts finr for me.
> CREATE TABLE JustADate
> (Dt datetime)
> INSERT JustADate VALUES('Dec 30 1989 11:55PM')
> INSERT JustADate VALUES('Dec 31 1989 1:05PM')
> INSERT JustADate VALUES('Dec 31 1989 7:15AM')
> INSERT JustADate VALUES('Dec 31 1989 7:35PM')
> INSERT JustADate VALUES('Dec 31 1989 9:10AM')
> INSERT JustADate VALUES('Dec 31 1989 10:20AM')
> INSERT JustADate VALUES('Dec 31 1989 11:10AM')
> INSERT JustADate VALUES('Dec 31 1989 11:40AM')
> INSERT JustADate VALUES('Jan 1 1990 1:20PM')
> INSERT JustADate VALUES('Jan 1 1990 12:35PM')
> SELECT *
> FROM JustADate
> ORDER BY Dt
> Dt
> --
> 1989-12-30 23:55:00.000
> 1989-12-31 07:15:00.000
> 1989-12-31 09:10:00.000
> 1989-12-31 10:20:00.000
> 1989-12-31 11:10:00.000
> 1989-12-31 11:40:00.000
> 1989-12-31 13:05:00.000
> 1989-12-31 19:35:00.000
> 1990-01-01 12:35:00.000
> 1990-01-01 13:20:00.000
> Roy Harvey
> Beacon Falls, CT
> On 22 Jun 2006 10:20:07 -0700, "wolfen42 at gmail.com"
> <wolfen42@.gmail.com> wrote:
>|||> SELECT convert(varchar(20),dt,100) as Dt
> FROM JustADate
> ORDER BY Dt
I think it would be more clear what you are getting at if you use a
different alias, e.g.
SELECT convert(varchar(20),dt,100) as DtConverted
FROM JustADate
ORDER BY DtConverted
or
SELECT convert(varchar(20),dt,100)
FROM JustADate
ORDER BY convert(varchar(20),dt,100)|||You know, all of the replies so far have been insisting that this isnt
the way a DATETIME field behaves and that it must be a varchar field
somehow.
I "KNEW" that it couldn't possibly be a varchar field because even
though the table was created by my predecessor, why would this be the
only date/time field in the table that was a varchar instead of a
datetime field?
Ahem... well... I've been reminded once again. If it's an issue that
assumes some sort of consistent behavior on the part of this particular
person... assume the worst.
Sigh.
Thanks for forcing me to look at the table structures one more time and
notice that yes... this one date/time data field was an nvarchar for no
good reason.
Tracy McKibben wrote:
> wolfen42 at gmail.com wrote:
> Are you sure that field is of type DATETIME? I just inserted these same
> values into a table with a DATETIME field, and they sort properly. If I
> put them into a VARCHAR field, then they do not.|||wolfen42 at gmail.com wrote:
> You know, all of the replies so far have been insisting that this isnt
> the way a DATETIME field behaves and that it must be a varchar field
> somehow.
> I "KNEW" that it couldn't possibly be a varchar field because even
> though the table was created by my predecessor, why would this be the
> only date/time field in the table that was a varchar instead of a
> datetime field?
> Ahem... well... I've been reminded once again. If it's an issue that
> assumes some sort of consistent behavior on the part of this particular
> person... assume the worst.
> Sigh.
> Thanks for forcing me to look at the table structures one more time and
> notice that yes... this one date/time data field was an nvarchar for no
> good reason.
>
Sometimes it pays to ask the obvious, easy questions... :-)

order by bearing in mind seconds

hi,
How could I order by datetime type including the seconds?
Now I am using order by <field> and it' getting the values of this way:
2005-08-04 03:03:42.000
2005-08-04 04:00:33.000
2005-08-04 07:31:20.000
Instead of:
2005-08-04 07:31:20.000
2005-08-04 04:00:33.000
2005-08-04 03:03:42.000
Best wishes,Hi Enric,
I'm not sure what you think is wrong with the sorting that you get. It seems
perfectly valid.
Sorting by datetime includes ALL datetime elements (even the milisecconds).
If you want to sort ONLY by seconds, ingnoring the other parts, use:
order by datepart(second, dt)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:F722D128-297D-4DD9-A8DC-05F34F714B87@.microsoft.com...
> hi,
> How could I order by datetime type including the seconds?
> Now I am using order by <field> and it' getting the values of this way:
> 2005-08-04 03:03:42.000
> 2005-08-04 04:00:33.000
> 2005-08-04 07:31:20.000
> Instead of:
> 2005-08-04 07:31:20.000
> 2005-08-04 04:00:33.000
> 2005-08-04 03:03:42.000
> Best wishes,
>
>
>
>|||I don't understand the question. The first example IS ordered by time
including the seconds. Ordering by a DATETIME column always takes
account of the whole date and time value, unless you perform some other
manipulation of the value.
David Portas
SQL Server MVP
--|||In order to accomplished you goad you presented you should just change the
sort order for that ?!
order by <col> DESC
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> hi,
> How could I order by datetime type including the seconds?
> Now I am using order by <field> and it' getting the values of this way:
> 2005-08-04 03:03:42.000
> 2005-08-04 04:00:33.000
> 2005-08-04 07:31:20.000
> Instead of:
> 2005-08-04 07:31:20.000
> 2005-08-04 04:00:33.000
> 2005-08-04 03:03:42.000
> Best wishes,
>
>
>
>|||Thanks a lot to all,
"Jens Sü?meyer" wrote:
> In order to accomplished you goad you presented you should just change the
> sort order for that ?!
> order by <col> DESC
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Enric" wrote:
>

ORDER BY [Date] isnt working since its not a DateTime (its a varchar!)

Hi everyone.

I know, I know, it should have been a datetime from the start...but here's the problem.

I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:

ORDER BY [Date]

Are there any work arounds for this? Is there some way of doing:

ORDER BY covert(datetime, [Date], 103) or something?

Cheers
AndrewSELECT Convert(datetime, [Date], 103), Columnnames FROM TABLENAME
ORDER BY 1|||Thanks heaps, I'll give it a go!

Andrew|||Worked perfectly, though I chosed to:

SELECT convert(datetime, [Date], 103) AS 'Converted'
WHERE ...
ORDER BY Converted

just for looks :)

Thanks again!