October 6, 2005 at 11:55 am
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
October 6, 2005 at 12:11 pm
only "10/3/2005 " is impossible if it's really a smalldatetime column. What does the source data looks like?
Are you talking about the seconds missings or just the lack of that time info on some rows?
October 6, 2005 at 12:12 pm
How are you looking at this data?
if your using enterprise manager, I'm not surprised.
use query analyzer instead.
October 6, 2005 at 12:52 pm
Thank you all.
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?
October 6, 2005 at 1:39 pm
Sql stores date times as 2 different numbers, but it is the responsibility of the presentation layer to display the date/time formatted. I don't know why, but enterprise manager doesn't do it right consistently.
Query analzer by default presents the date as
yyyy-mm-dd hh:mm:ss:mss if you want to see it in a specific format, such as 10/3/2005 12:00:00 AM then you must explicitly cast it to that format.
see convert function
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply