SQL Query v3
-
- Registered User
- Posts: 716
- Joined: 08 Oct 2004, 02:00
- Contact:
SQL Query v3
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?
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?
-
- Registered User
- Posts: 716
- Joined: 08 Oct 2004, 02:00
- Contact:
Re: SQL Query v3
Also, the both columns have either AM or PM at the end... Should I just SUBSTRING ftw?
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Query v3
Nope....
Have a look at the datepart function. It returns only the specified part of the datetime field.
Have a look at the datepart function. It returns only the specified part of the datetime field.
- Spoiler (show)
- Ron2K
- Forum Technical Administrator
- Posts: 9050
- Joined: 04 Jul 2006, 16:45
- Location: Upper Hutt, New Zealand
- Contact:
Re: SQL Query v3
I have to ask - why? The DATETIME data type stores it all in one column, as I'm sure you've noticed.GrimStoner wrote:2 of the columns contain the date and time respectively.
(As for your problem - yeah, what RRF said.)
Kia kaha, Kia māia, Kia manawanui.
-
- Registered User
- Posts: 716
- Joined: 08 Oct 2004, 02:00
- Contact:
Re: SQL Query v3
That's the way it is given to us by our supplier.
Btw... what I did was :
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 :
Should work, right?
Btw... what I did was :
Code: Select all
UPDATE Transactions
SET DateField = DateField + convert(char(8), TimeField, 108)
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
-
- Registered User
- Posts: 716
- Joined: 08 Oct 2004, 02:00
- Contact:
Re: SQL Query v3
Apparantly not...
Any ideas?
Code: Select all
Msg 286, Level 16, State 1, Procedure UpdateDateWithTime, Line 12
The logical tables INSERTED and DELETED cannot be updated.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Query v3
- 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
- Spoiler (show)
-
- Registered User
- Posts: 716
- Joined: 08 Oct 2004, 02:00
- Contact:
Re: SQL Query v3
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.
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.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Query v3
GrimStoner wrote: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.
- Spoiler (show)