combine separate date & time fields into one datetime field?

[drurjen]

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?
2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.

[Erland Sommarskog]

drurjen (jf*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[drurjen]

Erland,

Thank you for replying. Basically the DB is for employee time
attendance records. I start out with a flat txt file and run that
through an Excel macro that:

a) eliminates repeat entries in a time lapse of 5min
b) erases null entries.

I then take the XLS file and use DTWizard to export it into a table
with the same fields as before: Date, Id, Time, IO. I have no primary
keys defined in this table, just a FK (Id). I believe the primary keys
should be ID, Date & Time.

I'll try your suggestion. Thx again, and sorry for the repeat post.