SEO

#1 (**permalink**) 03-01-2008, 11:54 AM

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.

#2 (**permalink**) 03-01-2008, 11:54 AM

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3 (**permalink**) 03-01-2008, 11:54 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

combine separate date & time fields into one datetime field?