I am bringing in AS400 data into SQL.
I found some data missing " hour:minute:second (AM or PM) " information.
This data field is "smalldatetime."

For example,
10/3/2005 12:00:00 PM
10/3/2005 12:00:00 PM
10/3/2005 12:00:00 PM
10/3/2005
10/3/2005 12:00:00 PM
10/3/2005 12:00:00 PM

Any idea to resolve this issue?

Appreciate.

Justin,

A couple questions:

1) What does the source time say? Any way of examining those fields to see
why they behave as they do?

2) How are you viewing the column? I get your described behavior when
opening the table in Enterprise Manager. The date/time rows with the time
missing are the ones that start at midnight.

Try viewing the table in Query Analyzer to see if they are truly missing.
Let us know how you make out.

Thomas Bradshaw
Data Integration Specialist
MyWebGrocer.com


--
Message posted via http://www.sqlmonster.com

Thank you Thomas.

I was able to find the difference with Query Analyzer, and it looked as

2005-10-03 00:00:00.

I think that is good catch and my learning experience.

Question: Why then this does not appear as

" 10/3/2005 12:00:00 AM " instead?



"Thomas Bradshaw via SQLMonster.com" wrote:

The way it is displayed depends on the gui that is displaying it unless you
have formatted it into a string with CONVERT().

--
Andrew J. Kelly SQL MVP


"Justin Doh" <JustinDoh (AT) discussions (DOT) microsoft.com> wrote

The smalldatetime data type does not store h/m/s data as a datetime data type
would on SQL Server.

In your destination table make the field a "datetime" data type instead of
"smalldatetime".

Beau

"Andrew J. Kelly" wrote:

Hello Beau Frusetta" bizzeau at hotmail dot com,

Smalldatetime will happily display the hour and minutes. It does not display
seconds.

Allan