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

Oracle replication failed

Some replication jobs failed and it shows 'data conversion failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error in online book. THanks in advance!

Where did you encounter this error? Command line interface or GUI?

Can you provide more information such as the repro steps, environment setting, and the table schema?

Regards,

Gary Chen|||I found the error 'The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.' in view sysnchronization status. and i checked the history in job agent that it shows
"Message
2005-12-14 03:35:33.805 Connecting to Subscriber 'CCBHK08D'
2005-12-14 03:35:33.977 Skipping file 'CCB_CLINM2_2.sch' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2005-12-14 03:35:33.993 Bulk copying data into table 'CCB_CLINM2'
2005-12-14 03:35:34.259 Agent message code 20037. The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.
2005-12-14 03:35:34.321 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-14 03:35:34.337 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed"

A few small tables are replicated to the MSSQL2005 but some tables got the same error. The machine is running in 64-bits. And the replication testing in 32-bits is fine. Also, I found that some ODBC drivers are missing in MSSQL 2005 even installed with MDAC2.8. Is it support 32-bits only? Thanks in advance|||also, I found the error in replication monitor
"Error messages:
The process could not bulk copy into table '"dbo"."SYSTWODB_CABL"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Data conversion failed"|||

Below is the ddl of table (in both mssql and oracle) which failed in replication
USE [dev]
GO
/****** Object: Table [dbo].[SYSTWODB_EXCHR] Script Date: 12/14/2005 14:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYSTWODB_EXCHR](
[GLB_DTIME] [numeric](16, 0) NOT NULL,
[CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XCCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[EXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XEXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[REL_DAY] [numeric](5, 0) NOT NULL,
[XREL_DAY] [numeric](5, 0) NOT NULL,
[BASEX_CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[BSE_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[INP_DATE] [numeric](5, 0) NOT NULL,
[INP_TIME] [numeric](6, 0) NOT NULL,
[MID_MKT_LN] [numeric](14, 10) NOT NULL,
[MID_MKT_TY] [numeric](14, 10) NOT NULL,
[MKT_BUY] [numeric](14, 10) NOT NULL,
[MKT_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[MKT_SELL] [numeric](14, 10) NOT NULL,
[RATE_USAGE] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[SOFTLOCK] [numeric](2, 0) NOT NULL,
[WIDTH] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
CONSTRAINT [MSHREPL_49_PK] PRIMARY KEY CLUSTERED
(
[GLB_DTIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
*******************************************

CREATE TABLE "LINC"."SYSTWODB_EXCHR"("GLB_DTIME" NUMBER(16) NOT
NULL, "CCY" VARCHAR2(4) NOT NULL, "XCCY" VARCHAR2(4) NOT NULL,
"EXCH_GRP" VARCHAR2(4) NOT NULL, "XEXCH_GRP" VARCHAR2(4) NOT
NULL, "REL_DAY" NUMBER(5) NOT NULL, "XREL_DAY" NUMBER(5) NOT
NULL, "BASEX_CCY" VARCHAR2(4) NOT NULL, "BSE_RATEID" VARCHAR2(7)
NOT NULL, "INP_DATE" NUMBER(5) NOT NULL, "INP_TIME" NUMBER(6)
NOT NULL, "MID_MKT_LN" NUMBER(14, 10) NOT NULL, "MID_MKT_TY"
NUMBER(14, 10) NOT NULL, "MKT_BUY" NUMBER(14, 10) NOT NULL,
"MKT_RATEID" VARCHAR2(7) NOT NULL, "MKT_SELL" NUMBER(14, 10) NOT
NULL, "RATE_USAGE" VARCHAR2(1) NOT NULL, "SOFTLOCK" NUMBER(2)
NOT NULL, "WIDTH" VARCHAR2(1) NOT NULL,
CONSTRAINT "SYSTWODB_EXCHR_PK" PRIMARY KEY("GLB_DTIME"),
CONSTRAINT "SYS_C0022019" CHECK("GLB_DTIME" IS NOT NULL),
CONSTRAINT "SYS_C0022020" CHECK("CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022021" CHECK("XCCY" IS NOT NULL),
CONSTRAINT "SYS_C0022022" CHECK("EXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022023" CHECK("XEXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022024" CHECK("REL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022025" CHECK("XREL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022026" CHECK("BASEX_CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022027" CHECK("BSE_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022028" CHECK("INP_DATE" IS NOT NULL),
CONSTRAINT "SYS_C0022029" CHECK("INP_TIME" IS NOT NULL),
CONSTRAINT "SYS_C0022030" CHECK("MID_MKT_LN" IS NOT NULL),
CONSTRAINT "SYS_C0022031" CHECK("MID_MKT_TY" IS NOT NULL),
CONSTRAINT "SYS_C0022032" CHECK("MKT_BUY" IS NOT NULL),
CONSTRAINT "SYS_C0022033" CHECK("MKT_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022034" CHECK("MKT_SELL" IS NOT NULL),
CONSTRAINT "SYS_C0022035" CHECK("RATE_USAGE" IS NOT NULL),
CONSTRAINT "SYS_C0022036" CHECK("SOFTLOCK" IS NOT NULL),
CONSTRAINT "SYS_C0022037" CHECK("WIDTH" IS NOT NULL))
TABLESPACE "URBIS" PCTFREE 10 PCTUSED 70 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 2604K NEXT 352K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING|||

Besides, I found a message regarding to the failed replication in error log

'Error: 14151, Severity: 18, State: 1'

Any Hints on it? Thanks in advance

|||

Let's try to narrow it down a bit further.

So, the replication works fine on 32bit server and all tables copied fine, right? Only 64bit SQL server is having problem? Has the replication ever work in your 64bit environment at all? Can you tell me what your environment, OS, topology, and platform?

A minimum series of steps that can help to reproduce the error.

What type of Oracle publisher do you use? Gateway or Complete?

I also notice that you uses "Chinese_Hong_Kong_Stroke_90_CI_AS" when you create your table. What is the character set that you have install on your SQL server, OS, and Oracle server?

Regards,

Gary

|||

1. same set of tables and same source DB (with vchar and number as data type)

2. source: oracle 9.2 running in AIX platform.

3, publisher is gateway

4. SQL collation is Chinese _hong_kong_stroke_90_CI_AS, OS is 'Chinese (Hong Kong SAR)'

5. transactional publication with push subscription

6. Destination: Win2003 standard x64 edition sp1 with MSSQL2005 EVAL edition (x64) while another machine is Win2003 standard edition sp1 MSSQL2005 eval edition

7. All tables can be replicated to MSSQL2005(32bits) but some of them failed in the 64 bits one.

points 1-5 are the same in both 32 and 64 bits machine

many many Thanks for your help

|||

Hi,

This may or may not help you but I came accross this "data conversion" error when i was trying to reinitialize a subscriber when we migrated the publisher from 2000 to 2005. Our distributor also migrated to a SQL 2005 instance and it was a push subscription.

The issue in this instance was that the underlying data type length for a UDF created at the subscriber originally by SQL 2000 was smaller than what SQL 2005 needed. During the initialization the UDF was not being created at the subscriber by the SQL 2005 as it was already there so the solution was to drop the UDF and let the initialization process create it. Luckily the UDF was not being used anywhere else so we were able to drop it.

Cheers,

Priyanga