CODEWITHSUNDEEP
t-sqlsql-server-2005triggers
M.Ford
M.Ford

Reputation: 539

Combine separate date and time columns into one column

I have a situation for which I need some suggestions. No code as I want to look that up and figure it out myself. This is just an academic question.

I get a flat file daily that I have to load into a table here is a snippet of the file:

Date-YYYMMDD  TIME
20091124|     01:19:00|...
20091124|     01:19:15|...

The two fields are date and time. when I load this data into the database, the results are these:

Date-YYYMMDD  TIME
20091124      1899-12-30 01:19:00.000
20091124      1899-12-30 01:19:15.000

I have a view that merges the data with the results being:

datetime                   yearmonthday
2009-11-24 01:19:00.000    2009-11-24
2009-11-24 01:19:15.000    2009-11-24

I do this effort so that the folks that need this data don't have to fool around with dates and text fields that should be dates but are not.

The problem is I don't like doing this in a view. I would rather just store it in the original table correctly. I was thinking that for every row inserted, I intercept that insert, do the merging then insert into the table in the correct format. Once done, I could drop the view. Is there a best way for this? Like any good developer/admin. I want to minimize my intervention. We have computers to do this type of work. I do not control the source file nor can I effect any changes with it. I was thinking an on insert trigger. Is there a better way? Though I have done a lot with TSQL over the months, I still consider myself a beginner in my understanding of good TSQL and the most efficient way to do things.

Upvotes: 0

Views: 856

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280439

I would say load into a staging table.

CREATE TABLE dbo.StagingTable
(
  Col1 DATETIME,
  Col2 DATETIME
);

Load your data into this table. If your file is as simple as the one in your question, you should be able to use BULK INSERT and cut out much of your manual process. Then in your real table, have a DATETIME column.

INSERT dbo.RealTable(DateTimeColumn)
  SELECT DATEADD(SECOND, DATEDIFF(SECOND, 0, Col2), Col1)
  FROM dbo.StagingTable;

-- if successful:
TRUNCATE TABLE dbo.StagingTable;

An even easier way would be - if you're opening the files and modifying them anyway - is to change this:

Date-YYYMMDD  TIME
20091124|     01:19:00|...
20091124|     01:19:15|...

To this:

Date-YYYMMDD     |...
20091124 01:19:00|...
20091124 01:19:15|...

Upvotes: 2

Related Questions