ORDER BY DATETIME field BUG?

microsoft.public.sqlserver.programming

22 Jun 2006 5:20 PM

wolfen42 at gmail.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?

22 Jun 2006 5:27 PM

Aaron Bertrand [SQL Server MVP]

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).

Show quote

"wolfen42 at gmail.com" <wolfe***@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?
>

22 Jun 2006 5:35 PM

Alejandro Mesa

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

Show quote

"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?
>
>

22 Jun 2006 5:40 PM

Tracy McKibben

wolfen42 at gmail.com wrote:
Show quote

> 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.

30 Jun 2006 12:45 PM

wolfen42 at gmail.com

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:
Show quote

> 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.

30 Jun 2006 1:04 PM

Tracy McKibben

wolfen42 at gmail.com wrote:
Show quote

> 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... :-)

22 Jun 2006 5:52 PM

Roy Harvey

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"
<wolfe***@gmail.com> wrote:

Show quote

>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?

22 Jun 2006 6:02 PM

SQL Menace

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:
Show quote

> 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"
> <wolfe***@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?

22 Jun 2006 7:23 PM

Aaron Bertrand [SQL Server MVP]

> 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)