SQL Query v3

[GrimStoner]

Hi

I have a table with about 3.5mil rows. 2 of the columns contain the date and time respectively. Because SQL combines time and dates in datetime, the dates are represented as 5/6/2007 00:00:00 and the times as 1899/01/01 15:16:17. Is there a query that I can run to combine the date and time column into a single column that will be like 5/6/2007 15:16:17?

[GrimStoner]

Also, the both columns have either AM or PM at the end... Should I just SUBSTRING ftw?

[RuadRauFlessa]

Nope....

Have a look at the datepart function. It returns only the specified part of the datetime field.

[Ron2K]

2 of the columns contain the date and time respectively.

I have to ask - why? The DATETIME data type stores it all in one column, as I'm sure you've noticed.

(As for your problem - yeah, what RRF said.)

[GrimStoner]

That's the way it is given to us by our supplier.

Btw... what I did was :

Code: Select all

UPDATE Transactions
SET DateField = DateField + convert(char(8), TimeField, 108)

There are still two columns, but we'll just do reports on the DateField column. Need to keep the TimeField there, so future imports still work.

I want to add a trigger ON INSERT, so when a new transaction is added, the date is automatically updated with the time from the TimeField column. How do you change data on a trigger? My idea is this :

Code: Select all

CREATE TRIGGER UpdateDateWithTime 
   ON  dbo.Transactions 
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	UPDATE inserted
        SET inserted.DateField = inserted.DateField +  convert(char(8), TimeField, 108)
END
GO

Should work, right?

[GrimStoner]

Apparantly not...

Code: Select all

Msg 286, Level 16, State 1, Procedure UpdateDateWithTime, Line 12
The logical tables INSERTED and DELETED cannot be updated.

Any ideas?

[RuadRauFlessa]

• 1) Which version of SQL Server are you working with?
  2) You can work with the two fields as is it only wastes space
  3) Your char convert is not a good way to do it as the system's date and time format might change
  4) It is not a good idea to alter data inserted into a DB by a 3rd party app

[GrimStoner]

1. SQL Server 2005.
2. True.
3. Don't think the date/time format would change.
4. I insert the data into the DB with an app I wrote. No 3rd party.

[RuadRauFlessa]

1. SQL Server 2005.
2. True.
3. Don't think the date/time format would change.
4. I insert the data into the DB with an app I wrote. No 3rd party.

Then change your app to work with the datetime field correctly. Don't opt for a workaround as the only thing you will do is confuse the matter even further and create a possibility for more bugs.