Posted by Erland Sommarskog on 12/26/06 16:52

drurjen (jfontecha@gmail.com) writes:
> Good morning.
>
> I am importing an XLS file into one of my tables. The fields are:
>
> Date Id Time IO
> 12/22/2006 2 12:48:45 PM 9
> 12/22/2006 16 5:40:55 AM 1
> 12/22/2006 16 12:03:59 PM 2
>
> When I do the import, I get the following:
>
> Date Id Time IO
> 12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
> 12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
> 12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
>
> Here are my doubts:
>
> 1. Would it be better to combine the Date & Time fields into one
> column? If so, how?

Most probably. (In the end it depends on business needs, which I don't
anything about.)

A way to merge the columns would be:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

> 2. What issues or problems might I have when I program SQL reports, if
> I leave the fields as they are?

That you get 1899-12-30 printed all over the place, which you probably
don't want to. So you will need a lot of code to filter the date away.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]