Data Conversion Issue

[shabar]

Hi All






I have following two columns as text data type in table T1


Col1
23-Nov-2005 02:02:19 PM
31-Oct-2005 04:07:05 PM
31-Oct-2005 04:06:46 PM
06-Dec-2005 02:14:05 PM


Col2
01-Jan-2006 12:00:00 AM
01-Jan-2006 12:00:00 AM
01-Jan-2006 12:00:00 AM
01-Jan-2006 12:00:00 AM
01-Jan-2006 12:00:00 AM




After running the following querys I get different data formats eventhough the data type has change to date/time


ALTER TABLE T1 ALTER COLUMN T1.Col1 DATETIME




ALTER TABLE T1 ALTER COLUMN T1.Col2 DATETIME








Col1
23-Nov-2005 02:02:19 PM
31-Oct-2005 04:07:05 PM
31-Oct-2005 04:06:46 PM
06-Dec-2005 02:14:05 PM






Col2
01-Jan-2005
01-Jan-2005
01-Jan-2005
01-Jan-2005
01-Jan-2005




What could be the reason. I need Col1 also same format as Col2






Cheers




Shabar

[alcapps]

col1 is being set as datetime
col2 is being set as date

if you do it manually do you get the same results?

[alansidman]

The issue is Excel and Access look at Date Time values as numbers. Formatting make them appear as we wish. For the col2 values, there is no decimal because Midnight is Zero hour.

[alcapps]

good catch, I didn't see the part where the columns where text to start.

[shabar]

alansidman


Thax a lot for the clarification


Cheers

Shabar